Relational model

Finance database schema

The database contains sales, purchase and payment data, along with suppliers and products. It supports practice with joins, aggregations, VAT, margins and overdue balances.

finance.db8 tables

clients factures produits achats

Reading the model

How to read the relational model?

Primary key

Unique identifier for a row in a table. Example: id_client identifies a customer.

Foreign key

Column used to connect one table to another. Example: factures.id_client references clients.id_client.

Relation 1 → N

One row in a table can relate to several rows in another table. Example: one customer can have several invoices.

SQL join

A join combines business information distributed across several tables: customer, invoice, payment, product or supplier.

Sales cycle

clients

1 → N

factures

1 → N

reglements

A customer can receive several invoices. An invoice may be fully, partially or not paid. This relationship supports calculations for overdue balances, outstanding amounts and customer exposure.

Sales detail

factures

1 → N

lignes_ventes

N → 1

produits

An invoice can contain several sales lines. Each line references a product. This relationship supports revenue, purchase cost, margin and margin-rate calculations by product.

Purchasing cycle

fournisseurs

1 → N

factures_achats

1 → N

paiements_fournisseurs

A supplier can issue several purchase invoices. Supplier payments are used to track cash outflows, purchases excluding VAT and deductible VAT.

Join example

Join customers and invoices

This query links an invoice to its customer through the foreign key factures.id_client.

SELECT c.nom_client, f.id_facture, f.montant_ttc
FROM clients c
JOIN factures f ON c.id_client = f.id_client;

Table 01

clients

Customer master data.

4 colonnes

Column	Type	Role
`id_client`	INTEGER	Primary key
`nom_client`	TEXT	Customer name
`secteur`	TEXT	Industry
`pays`	TEXT	Customer country

Table 02

factures

Sales invoices issued to customers.

6 colonnes

Column	Type	Role
`id_facture`	INTEGER	Primary key
`id_client`	INTEGER	Foreign key to clients
`date_facture`	TEXT	Invoice date
`montant_ht`	REAL	Net amount, excluding VAT
`tva`	REAL	VAT collected
`montant_ttc`	REAL	Gross amount, including VAT

Table 03

reglements

Payments received from customers.

4 colonnes

Column	Type	Role
`id_reglement`	INTEGER	Primary key
`id_facture`	INTEGER	Foreign key to factures
`date_reglement`	TEXT	Payment date
`montant_regle`	REAL	Amount received

Table 04

fournisseurs

Supplier master data.

4 colonnes

Column	Type	Role
`id_fournisseur`	INTEGER	Primary key
`nom_fournisseur`	TEXT	Supplier name
`categorie`	TEXT	Supplier category
`pays`	TEXT	Supplier country

Table 05

factures_achats

Purchase invoices received from suppliers.

6 colonnes

Column	Type	Role
`id_achat`	INTEGER	Primary key
`id_fournisseur`	INTEGER	Foreign key to fournisseurs
`date_achat`	TEXT	Purchase date
`montant_ht`	REAL	Net amount, excluding VAT
`tva_deductible`	REAL	Deductible VAT
`montant_ttc`	REAL	Gross amount, including VAT

Table 06

paiements_fournisseurs

Payments made to suppliers.

4 colonnes

Column	Type	Role
`id_paiement`	INTEGER	Primary key
`id_achat`	INTEGER	Foreign key to factures_achats
`date_paiement`	TEXT	Payment date
`montant_paye`	REAL	Amount paid

Table 07

produits

Product catalog and standard costs.

5 colonnes

Column	Type	Role
`id_produit`	INTEGER	Primary key
`nom_produit`	TEXT	Product name
`categorie`	TEXT	Product category
`prix_vente_ht`	REAL	Sales price excluding VAT
`cout_achat_ht`	REAL	Purchase cost excluding VAT

Table 08

lignes_ventes

Products sold on each invoice.

5 colonnes

Column	Type	Role
`id_ligne`	INTEGER	Primary key
`id_facture`	INTEGER	Foreign key to factures
`id_produit`	INTEGER	Foreign key to produits
`quantite`	INTEGER	Quantity sold
`prix_unitaire_ht`	REAL	Unit price excluding VAT