Explaining how to design a database to model the stocks of a firm to the Grand’ma

database-designrelational-theory

My teacher always asked us to create simple but precise databases. Being able to explain it simply but not without leaving the theory "to your wise but aged loved Grand'ma"…

I have to create a database to model the stocks of a firm which commercialize constructing industry products. A same product (Produit) can be bought (Achat) from suppliers (Fournisseur(s)). Client(s) place orders (Commande(s)) which will be used on different construction sites (Chantier(s)). An order is composed (Composition) by severals products. For each of them a discount can be done as a percentage of the selling price. Every order can be delivered in several times. The total quantity of a product can be allocated in several delivery notes (Bon de commande).

This is described in UML in the following pics:

UML description of the database

Therefore we should have:

  • Fournisseur
  • Achat
  • Produit
  • Composition
  • Commande
  • Client
  • Chantier
  • Livraison
  • ContenuBL
  • BonDeLivraison

The scheme modeling the database is the following one

RelationalScheme

with the following Latex Code

! $Achat(\underline{#IdFourniseur},#IdProduit,PrixVente,Date)$

! $Fournisseur(\underline{IdFourniseur}, NomFournisseur, NumérotSIRET,NuméroTel)$

! $Produit(\underline{IDProduit},PrixVente,QuantiteEnStock)$

! $BonDeLivraison(\underline{NuméroBon},AdresseLivraison)$

! $Commande(\underline{IDNuméroCommande},Date)$

! $Client(\underline{IDClient},AdresseClient,NomContact,NuméroSIRET)$

! $Livraison(\underline{#RefChantier},\underline{#IDProduit},\underline{#NumeroBon},Date)$

! $ContenuBL(\underline{#IDProduit},#NuméroBon,Quantité)$

! $Chantier(\underline{#RefChantier},ResponsableChantier,AdresseChantier)$

Yet, I'm not sure about it, especially because the associations between "classes", are not always shown in the scheme.

  • I'm not sure about Livraison for instance, which links ContenuBl and
    Chantier but therfore Produit and BonDeLivrasion to Chantier
  • I'm not sure about Client, which as an Association Qualifier and how
    to lin it between two classes which are not association classes

Furthermore, I created these relations by reflex. How to be able to explain to my loved Grand'ma why I always use Foreigner Kyes to association classes and not For simple classes ?

  • What leads to establish the relations I did, if they are right and optimal?

Best Answer

You might find this page interesting. Your schema seems to be reasonable - the only way you'll know is by writing your application and seeing if it works :-).

What you initially call a "bon de commande", you later refer to as a "bon de livraison" (which makes more sense in French). You also fail to provide a definition of ContenuBL (La composistion d'un bon de livraison je présume?).

Most of the people on this forum won't be able to speak/read French and will find it difficult to answer the question. If you like, I can translate it into English for you?

Related Question