Table of Contents

Database Modeling: The Langlois Case
#

Introduction
#

Database modeling is a crucial step in designing an information system. It allows you to structure data in a coherent and efficient manner before physical implementation. In this article, we will study the Langlois case, a specialized equipment distribution company, and design a complete database model to manage its catalog and quote requests.

Context: The Langlois Case
#

Company Overview
#

Langlois is a historic company based on the outskirts of Bordeaux. Its main activity is the manufacture and inventory management of specialized equipment:

Electronic equipment
Home appliances
Construction materials

Catalog Characteristics
#

400,000 articles are available in the catalog. Infinite possible variations: color, height, length, width, material, etc. Each variation affects the base price through a percentage.

Project Objectives
#

As part of its international development, Langlois wishes to:

Create a multilingual UI interface
Allow customers to browse the catalog
Generate personalized quote requests
Manage geographical restrictions on certain products

Functional Requirements
#

The system must be able to respond to the following queries:

Business Queries:

List the most requested products (catalog descent)
List quote requests made with price calculations based on variations
List products that have no variations
List the most requested products (by number of quotes)
Extract the number of quote requests for 2024
Know how many times a product was requested in 2025

Conceptual Data Model (CDM)
#

erDiagram
    VARIATION_TYPE ||--o{ PRODUCT : "describes"
    VARIATION_TYPE ||--o{ PRODUCT_VARIATION : "defines"
    
    FAMILY ||--o{ FAMILY : "parent/child"
    FAMILY ||--o{ PRODUCT : "contains"
    
    PRODUCT ||--o{ PRODUCT_VARIATION : "has variations"
    PRODUCT }o--o{ QUOTE : "QUOTE_PRODUCT"
    PRODUCT }o--o{ COUNTRY : "BANNED_COUNTRY"
    
    CLIENT ||--o{ QUOTE : "requests"
    CLIENT }o--|| COUNTRY : "resides"
    
    COUNTRY ||--o{ BANNED_COUNTRY : "forbids"
    
    QUOTE ||--|{ QUOTE_PRODUCT : "contains"

Main Entities
#

VARIATION_TYPE - Variation Type
#

Defines the types of possible variations for products.

Attributes:

id_variation_type (PK): Unique identifier
variation_label: Name of the type (height, color, voltage, etc.)

Examples:

id_variation_type	variation_label
1	height
22	color
115	voltage

Cardinality:

A VARIATION_TYPE can apply to multiple PRODUCT (1,n)
A VARIATION_TYPE can have multiple PRODUCT_VARIATION (1,n)

PRODUCT - Product Catalog
#

The heart of the system: references each available article.

Attributes:

id_product (PK): Unique identifier
product_label: Commercial product name
product_ref: Unique reference (e.g. “00001”)
product_description: Detailed description
base_price_product: Base price in euros
quantity_product: Stock quantity
id_variation_type (FK): Applicable variation type
id_family (FK): Product category

Example:

id_product	product_label	product_ref	base_price_product	quantity_product
1	Standard Carton	00001	12.50	1500
2	Laptop Pro 15"	10002	1299.00	75

Cardinality:

A PRODUCT belongs to a FAMILY (1,1)
A PRODUCT has a VARIATION_TYPE or none (0,1)
A PRODUCT can have multiple PRODUCT_VARIATION (0,n)

PRODUCT_VARIATION - Product Variations
#

This table stores specific variation values for each product.

Attributes:

product_variation (PK): Identifier
id_variation (FK): Variation type (references VARIATION_TYPE)
id_product (FK): Product concerned
value: Numeric value (120 for 120cm)
unit: Unit of measurement (cm, kg, volts)
pct_variation: Percentage price modification

Example:

product_variation	id_variation	id_product	value	unit	pct_variation
1	1 (height)	1	50	cm	15.00
2	2 (length)	1	80	cm	5.00

Final Price Calculation Formula:

Final Price = Base Price × (1 + pct_variation₁/100) × (1 + pct_variation₂/100) × ...

Concrete Example:

Base price: 100 €
Variation 1 (height 120cm): +15%
Variation 2 (special width): +5%
Final price = 100 × 1.15 × 1.05 = 120.75 €

Cardinality:

A PRODUCT_VARIATION belongs to a PRODUCT (1,1)
A PRODUCT_VARIATION references a VARIATION_TYPE (1,1)

FAMILY - Category Hierarchy
#

This table contains information on the catalog hierarchy (category tree).

Attributes:

id_family (PK): Identifier
family_label: Category name
family_description: Description
id_parent (FK): Reference to the parent family (self-reference)

Hierarchical Structure:

mindmap
  root((Categories))
    "Equipment — id 1 / parent NULL"
      "Electronics — id 3 / parent 1"
        "Smartphones — id 8 / parent 3"
        "Computers — id 9 / parent 3"
      "Home Appliances — id 4 / parent 1"
        "Kitchen — id 10 / parent 4"
      "Hardware — id 5 / parent 1"
    "Materials — id 2 / parent NULL"
      "Packaging — id 6 / parent 2"
        "Cardboard — id 11 / parent 6"
      "Construction — id 7 / parent 2"
        "Wood — id 12 / parent 7"

Cardinality:

A FAMILY can have a parent family (0,1)
A FAMILY can have multiple child families (0,n)
A FAMILY contains multiple PRODUCT (0,n)

Self-reference allows you to create a hierarchy of infinite depth.

QUOTE - Quote Requests
#

This table records each customer quote request.

Attributes:

id_quote (PK): Unique identifier
quote_ref: External reference (e.g. “QR-2025-001”)
quote_date: Creation date
quote_amount: Total calculated amount
id_client (FK): Requesting client

Cardinality:

A QUOTE is created by a CLIENT (1,1)
A QUOTE contains multiple PRODUCT via QUOTE_PRODUCT (1,n)

QUOTE_PRODUCT - Products in a Quote
#

This table is a link between QUOTE and PRODUCT (N:N relationship).

Attributes:

id_quote (PK, FK): Quote concerned
id_product (PK, FK): Product requested
product_quantity: Quantity ordered

Cardinality:

Composite primary key: (id_quote, id_product)
Allows a quote to have multiple products
Allows a product to appear in multiple quotes

CLIENT - Client Information
#

This table stores data of quote requesters.

Attributes:

id_client (PK): Unique identifier
client_name: Last name
client_first_name: First name (optional)
client_mobile: Phone number
id_country (FK): Country of residence

Cardinality:

A CLIENT resides in a COUNTRY (1,1)
A CLIENT can create multiple QUOTE (0,n)

COUNTRY - Countries and Geographic Zones
#

This table contains country references for geolocation and restrictions.

Attributes:

id_country (PK): Identifier
country_name: Country name
power_supply: Standard voltage (220V, 110V, 100V)

Electrical products are often incompatible depending on the country (transformer required).

Cardinality:

A COUNTRY can have multiple CLIENT (0,n)
A COUNTRY can ban multiple PRODUCT via BANNED_COUNTRY (0,n)

BANNED_COUNTRY - Geographic Restrictions
#

This table is a link between PRODUCT and COUNTRY to manage sales bans.

Attributes:

id_product (PK, FK): Product concerned
id_country (PK, FK): Country where the product is banned

It is the geographic area that is banned, not the client and their country of origin. This means:

A product can be banned for sale in certain countries A French client can still order if the product is delivered elsewhere The restriction applies to the delivery destination, not to the client’s nationality

Cardinality:

N:N relationship between PRODUCT and COUNTRY
A product can be banned in multiple countries
A country can ban multiple products

Detailed Entity-Relationship Diagram
#

erDiagram
    VARIATION_TYPE {
        int id_variation_type PK
        string variation_label
    }
    
    PRODUCT {
        int id_product PK
        string product_label
        string product_ref UK
        text product_description
        decimal base_price_product
        int quantity_product
        int id_variation_type FK
        int id_family FK
    }
    
    PRODUCT_VARIATION {
        int product_variation PK
        int id_variation FK
        int id_product FK
        decimal value
        string unit
        decimal pct_variation
    }
    
    FAMILY {
        int id_family PK
        string family_label
        text family_description
        int id_parent FK
    }
    
    QUOTE {
        int id_quote PK
        string quote_ref UK
        date quote_date
        decimal quote_amount
        int id_client FK
    }
    
    QUOTE_PRODUCT {
        int id_quote PK_FK
        int id_product PK_FK
        int product_quantity
    }
    
    CLIENT {
        int id_client PK
        string client_name
        string client_first_name
        string client_mobile
        int id_country FK
    }
    
    COUNTRY {
        int id_country PK
        string country_name UK
        string power_supply
    }
    
    BANNED_COUNTRY {
        int id_product PK_FK
        int id_country PK_FK
    }
    
    VARIATION_TYPE ||--o{ PRODUCT : "DESCRIBES (0,1)-(1,n)"
    VARIATION_TYPE ||--o{ PRODUCT_VARIATION : "DEFINES (1,1)-(0,n)"
    
    FAMILY ||--o{ FAMILY : "PARENT_CHILD (0,1)-(0,n)"
    FAMILY ||--o{ PRODUCT : "BELONGS (1,1)-(0,n)"
    
    PRODUCT ||--o{ PRODUCT_VARIATION : "HAS_VARIATION (1,1)-(0,n)"
    PRODUCT ||--o{ QUOTE_PRODUCT : "(1,1)-(0,n)"
    PRODUCT ||--o{ BANNED_COUNTRY : "(1,1)-(0,n)"
    
    QUOTE ||--|{ QUOTE_PRODUCT : "CONTAINS (1,1)-(1,n)"
    
    CLIENT ||--o{ QUOTE : "REQUESTS (1,1)-(0,n)"
    CLIENT }o--|| COUNTRY : "RESIDES (1,1)-(0,n)"
    
    COUNTRY ||--o{ BANNED_COUNTRY : "FORBIDS (1,1)-(0,n)"

Examples:

PRODUCT (1,1) –> (0,n) FAMILY: A product belongs to exactly one family, a family can have zero or multiple products
QUOTE (1,1) –> (1,n) QUOTE_PRODUCT: A quote contains at least one product

Single Responsibility Principle (SRP) - Invoice Class Refactoring