Clé étrangère "complexe"

Version imprimable

12/10/2012, 15h54
StringBuilder

Clé étrangère "complexe"

Bonjour,

Dans la cadre de la création d'un WMS, j'ai les éléments suivants :

depot (id, ...)
emplacement (id, depot_id fk(depot.id), ...)
quai (id, emplacement_id fk(emplacement.id), ...)
reception (id, quai_id fk(quai.id), ...)
reception_colis (id, reception_id fk(reception.id), emplacement_id fk(emplacement_id), ...)

Souligné : clé primaire
Gras : clé étrangère fk(table.colonne)

Cette modélisation est insuffisante pour gérer la cohérence de la chose :

Ma réception est attendue à un quai, qui correspond à un emplacement particulier de mon dépôt.
A l'avance, j'ai prévu de mettre dans un emplacement donné chacun des colis contenus dans la réception.

Seulement, il faut que je m'assure que les emplacements prévus pour les colis de mon emplacement sont bien dans le même dépôt que le quai de déchargement...

Comment modéliser ça, au niveau du MPD ? (ou même SQL)

Un trigger pourrait faire l'affaire, mais niveau performances, je pense que c'est clairement pas le top.

Idéallement, il faudrait pouvoir faire une sorte de clé étrangère ne référençant non pas la table des emplacements, mais une vue qui fait la jointure entre le dépôt correspondant au quai de la réception, et les emplacements du dépôt en question...

Le SGBD cible est SQL Server 2012

J'aurai vu ce genre de solution :

reception_colis (id, reception_id fk(reception.id), emplacement_id fk(emplacement_id), quai_depot calc(select e.depot_id from reception r inner join quai q on q.id = r.quai_id inner join emplacement e on e.id = q.emplacement_id where r.id = reception_id), emplacement_depot calc(select e.depot_id from emplacement e where e.id = emplacement_id), ...)

Et une contrainte sur quai_depot = emplacement_depot

Seulement, non seulement je vois pas comment l'implémenter, mais surtout je doute un peu de la pertinence d'une telle solution.

Avez-vous une meilleure idée ?
12/10/2012, 16h03
CinePhil

Au vu de tes tables, tu avais donc ce MCD :
depot -1,n----()----1,1- emplacement -1,n----()----1,1- quai -0,n----()----1,1- reception -1,n----()----1,1- reception_colis

Comme la réception_colis référence la réception qui référence le quai qui référence l'emplacement, tu n'as pas besoin de la clé étrangère de l'emplacement dans la table reception_colis. Elle est implicite et la conserver peut entraîner des données incohérentes.

Hop ! Un coup de rasoir d'Okkham, comme dirait fsmrel !
12/10/2012, 16h32
StringBuilder

Non, l'emplacement du quai n'est pas l'emplacement dans lequel je vais forcément déposer mes colis.

Dans le cadre d'un cross-docking avec ou sans allotissement, en effet une partie de mes colis va rester sur le quai de déchargement, et sera rechargé sur le prochain camion qui va arriver.

Mais ici, il s'agit de ranger les colis dans le dépôt, pas de les laisser pourrir sur le quai.

J'ai par exemple un dépôt qui contient 4 emplacements :
- Un emplacement réfrigéré (numéro 1)
- Un emplacement pour matières dangereuses (numéro 2)
- Un emplacement tout bête (numéro 3)
- Un emplacement qui correspond au quai de chargement (numéro 4)

Ma réception est donc attendue sur mon unique quai, correspondant à l'emplacement numéro 4.

Cependant, mon camion contient des colis de coca-cola, qui vont être stockés dans l'emplacement numéro 3, du beurre qui va aller dans le numéro 1, et de l'eau de javal qui va partir dans l'emplacement numéro 2.

Pour que mon cariste sache quoi faire avec les palettes au moment du déchargement, le WMS n'attend donc pas l'arrivée du camion pour affecter les colis aux emplacements : ainsi la place est réservée, ça évite que des palettes se perdent ou que du beurre reste abandonné en plein soleil.

Seulement, je ne veux pas que suite à une erreur d’algorithme ou une erreur de saisie, je me retrouve avec une affectation sur un autre dépôt, sinon à coup sûr mon beurre va moisir sur le quai...

Voici la contrainte check que je cherche à créer :

Code:

1
2
3
4
ALTER TABLE mouvement_lot_stock
ADD CONSTRAINT chk_depot
CHECK (emplacement_id in (select e2.id from mouvement_lot l inner join mouvement m on m.id = l.mouvement_id inner join quai q on q.id = m.quai_id inner join emplacement e1 on e1.id = q.emplacement_id inner join emplacement e2 on e2.depot_id = e1.depot_id where l.id = mouvement_lot_id);
GO

Seulement, ça marche pas ^^

12/10/2012, 17h10
CinePhil

Alors il semble que tu aies une erreur de modélisation car la description de tes tables correspond au MCD que j'ai donné et ton dernier message semble dire qu'un quai peut être associé à plusieurs emplacement, voire, selon ton exemple, qu'un emplacement ne correspond qu'à un seul quai.

Écris les règles de gestions bien formulées et le MCD sera plus facile à établir.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
create function MouvementLotDepot(@mouvement_lot_id int)
returns int
as begin
    return (select e.depot_id from mouvement_lot l inner join mouvement m on m.id = l.mouvement_id inner join quai q on q.id = m.quai_id inner join emplacement e on e.id = q.emplacement_id where l.id = @mouvement_lot_id)
end
go

create function EmplacementDepot(@emplacement_id int)
returns int
as begin
    return (select e.depot_id from emplacement e where e.id = @emplacement_id)
end
go

ALTER TABLE mouvement_lot_stock
ADD quai_depot as dbo.MouvementLotDepot(mouvement_lot_id)
go

ALTER TABLE mouvement_lot_stock
ADD emplacement_depot as dbo.EmplacementDepot(emplacement_id)
go

ALTER TABLE mouvement_lot_stock
add constraint chk_depot check (quai_depot = emplacement_depot)
GO

Marche pas, car il me dit que mes colonnes calculées ne sont pas persitantes.

Si je tente de les créer comme persistantes, il me jette en me disant que les fonctions sont pas déterministes...

Grrrr :cry:

12/10/2012, 17h26
StringBuilder

Citation:

Envoyé par CinePhil

Alors il semble que tu aies une erreur de modélisation car la description de tes tables correspond au MCD que j'ai donné et ton dernier message semble dire qu'un quai peut être associé à plusieurs emplacement, voire, selon ton exemple, qu'un emplacement ne correspond qu'à un seul quai.

Écris les règles de gestions bien formulées et le MCD sera plus facile à établir.

Non, un quai ne peut pas être associé à plusieurs emplacement.

Un quai EST un emplacement.
C'est juste que tous les quais ne sont pas des emplacements.

Donc j'ai une entité QUAI
Et une entité EMPLACEMENT
Avec une relation de type :

EMPLACEMENT (0,1)------------(1,1) QUAI

En effet, sur un quai de chargement, je peux avoir de la machandise, soit qui n'est pas encore rangée, soit en attente de chargement.

Quand un camion arrive, il ne faut pas l'envoyer sur un quai qui a de la marchandise en attente, à moins au contraire qu'elle soit à destination du camion.

En revanche, ce qui est dans le camion, ça ne va pas être forcément stocké sur le quai. A l'avance, je veux savoir où le stocker.

Le EMPLACEMENT_ID qui se trouve dans mon COLIS est complètement indépendant du QUAI_ID qui se trouve dans ma RECEPTION, au détail près que je dois m'assurer que tous les EMPLACEMENT_ID de mes COLIS sont bien sur le même dépôt que mon QUAI_ID.
12/10/2012, 17h29
StringBuilder

Règle 1 :
- Un emplacement est situé dans un et un seul dépôt. Un dépôt contient 1 à N emplacements.

Règle 2 :
- Un quai correspond à un et un seul emplacement. Un emplacement correspond à 0 à 1 quai.

Règle 3 :
- Il peut y avoir autant de quais que d'emplacement dans un même dépôt (plateforme de crossdocking par exemple)

Règle 4 :
- Une réception est attendue à 1 et 1 seul quai

Règle 5 :
- Un colis est présent dans une et une seule réception

Règle 6 :
- Un colis est attribué à un et un seul emplacement

Règle 7 :
- Un colis est attendu à un emplacement qui peut être différent de celui du quai de la réception

Règle 8 :
- Un colis est attribué à un emplacement DU MEME DEPOT que le quai où est attendu la réception
12/10/2012, 18h29
CinePhil

Contradictions...

Citation:

Un quai EST un emplacement.
C'est juste que tous les quais ne sont pas des emplacements.

Citation:

Règle 2 :
- Un quai correspond à un et un seul emplacement. Un emplacement correspond à 0 à 1 quai.

Citation:

- Un emplacement qui correspond au quai de chargement (numéro 4)

Ma réception est donc attendue sur mon unique quai, correspondant à l'emplacement numéro 4.

Il semble donc que la règle soit :
"Un quai est un emplacement et un emplacement peut être un quai."
=> Typique d'un héritage de données.
quai -(1,1)----être----0,1- emplacement

emplacement (emp_id...)
quai (qai_id_emplacement...)

Citation:

Le EMPLACEMENT_ID qui se trouve dans mon COLIS est complètement indépendant du QUAI_ID qui se trouve dans ma RECEPTION, au détail près que je dois m'assurer que tous les EMPLACEMENT_ID de mes COLIS sont bien sur le même dépôt que mon QUAI_ID.

OK, je comprends mieux.

Je crains que tu ne puisses échapper au trigger, à l'assertion ou à la contrainte CHECK pour ta contrainte formelle.
12/10/2012, 21h03
StringBuilder

En effet, désolé pour les contradictions, je me suis un peu trop dépêché pour taper le message...

Donc on arrive à la même conclusion : pas de solution "native". Obligé de passer par un trigger ou autre lourdeur...

Merci !
13/10/2012, 01h26
fsmrel
Bonsoir,

Pour éviter les triggers.

L’idée est de propager l’attribut composant la clé primaire de la table DEPOT aussi loin que nécessaire pour qu’il soit présent une et une seule fois (rasoir d'Ockham oblige :D) dans l’en-tête de la table sensible, COLIS en l’occurrence.

Un emplacement est une propriété multivaluée du dépôt, donc on peut partir sur l’identification relative du 1er par rapport au 2e :
Code:

1 2 3 4 5 6 DEPOT (D#, ...) PRIMARY KEY (D#) ; EMPLACEMENT (D#, E#, ...) PRIMARY KEY (D#, E#) FOREIGN KEY (D#) REFERENCES DEPOT ;
Pour aller dans le sens de CinePhil, il y a une relation d’héritage entre QUAI et EMPLACEMENT (attention à la rue du quai...) :
Code:

1 2 3 QUAI (D#, E#, ...) PRIMARY KEY (D#, E#) FOREIGN KEY (D#, E#) REFERENCES EMPLACEMENT ;
Si vous acceptez d’identifier les réceptions relativement au dépôt :
Code:

1 2 3 RECEPTION (D#, R#, E# , ...) PRIMARY KEY (D#, R#) FOREIGN KEY (D#, E#) REFERENCES QUAI ;
Au tour des colis. (D#, E#) représente le quai où le colis est attendu :
Code:

1 2 3 4 COLIS (C#, D#, R#, E#, ...) PRIMARY KEY (C#) FOREIGN KEY (D#, R#) REFERENCES RECEPTION FOREIGN KEY (D#, E#) REFERENCES QUAI ;
En observant que l’attribut D# représente à la fois le dépôt de réception du colis et celui du quai où il est attendu : un colis ne peut pas être attendu dans un dépôt et réceptionné dans un autre.
■
13/10/2012, 17h08
StringBuilder

Bonjour,

Merci pour cette réponse détaillée et intéressante.

J'ai lu de long en large que SQLPro déconseillait les clés primaires composées, hors, c'est ici ce que vous me proposez comme solution.

Etant donné que le volume pourrait rapidement devenir important (l'application devrait gérer des dépôts de plusieurs milliers de m², avec par conséquent des centaines de milliers de palettes) doit-je privilégier ces clés composites, qui vont ralentir les lectures (qui seront, je pense, très nombreuses) ou un trigger qui réduira plutôt la vitesse des insertions dans la table des réceptions, dont le volume mouvementé chaque jour devrait être assez conséquent tout de même.

Prototypons...

Bonsoir,

Citation:

Envoyé par StringBuilder

J'ai lu de long en large que SQLPro déconseillait les clés primaires composées, hors, c'est ici ce que vous me proposez comme solution.

J’ai donné mon point de vue il n’y a pas longtemps quant à la vision qu’a SQLpro des choses, vision que je ne partage pas.

Cela fait 25 ans que j’utilise l’identification relative — donc les clés composées — dans le contexte des grosses bases de données (gestion des comptes-clients dans les grandes banques, gestion des contrats, des cotisations et toutes ces sortes de choses dans les caisses de retraite, prises de commandes, etc.) et ça pulse. Mais je ne me suis pas lancé dans cette affaire le nez en l’air et les cheveux aux vents : j’ai toujours pris un soin extrême à prototyper les performances, même si cela est fastidieux et parfois décourageant. En tout cas, cela m’a toujours permis d’engager mon entreprise (une SSII) quant à ces fichues performances auprès de nos clients, en toute objectivité, sans connaître de raté. Mais comme je le dis par ailleurs : « Vérité en deçà des Alpilles, erreur au delà... », vingt fois sur le métier remettons notre ouvrage de prototypage...

Le seul bémol que je puisse apporter vient de ce que j’ai toujours prototypé avec DB2 (parfois Teradata, à l’occasion avec Oracle), et pour en revenir aux Alpilles, il est possible que ce qui se passe bien avec mon SGBD habituel pose des problèmes avec MS SQL Server (je suppose que vous l'utilisez puisque vous vous référez à l’avis de SQLpro), SGBD à propos duquel je ne me prononcerai pas avec autorité, loin s'en faut...

=> A votre tour de prototyper pour assurer le coup.

Citation:

Envoyé par StringBuilder

doit-je privilégier ces clés composites, qui vont ralentir les lectures

Pour quelle raison les clés composites ralentiraient-elles les lectures ?

Le calcul de l’identifiant relatif ne vaut que pour la table EMPLACEMENT et je suppose qu’il n’y en aura pas des centaines de milliers... Si problème il peut y avoir, c’est plutôt avec la table RECEPTION lors des inserts. Vu les volumétries, et pour éviter les contentions, il ne serait peut être pas mauvais de permuter l’ordre des attributs composant la clé primaire de la table RECEPTION (et composant la clé étrangère correspondante de la table COLIS) et de hacher les valeurs prises par R# (à prototyper...) Mais dans tous les cas, on peut calculer les valeurs prise par R# comme s’il s’agissait d’un identifiant absolu : le but de la manœuvre est fondamentalement d’« attirer » l'attribut D# jusqu’à la table COLIS :
Code:

1 2 3 4 5 6 7 8 RECEPTION (D#, R#, E# , ...) PRIMARY KEY (R#, D#) FOREIGN KEY (D#, E#) REFERENCES QUAI ; COLIS (C#, D#, R#, E#, ...) PRIMARY KEY (C#) FOREIGN KEY (R#, D#) REFERENCES RECEPTION FOREIGN KEY (D#, E#) REFERENCES QUAI ;
La paire {R#, D#) n’encombre guère les index. Si R# est du type INTEGER (soit plus de 2 milliards de réceptions), la taille de cet attribut correspond à 4 octets. Si le nombre de dépôts est inférieur à 256, D# peut être du type TINYINT (1 octet), sinon, si ce nombre est inférieur à 32768, D# peut-être du type SMALLINT (2 octets), sinon ça sera INTEGER. Au final, la clé primaire de la table RECEPTION sera comprise entre 5 et 8 octets, on a vu pire.

A votre avis, qu’est-ce qui pourrait freiner les lectures avec ce genre de clé composite ? Un hypothétique niveau d’index supplémentaire ?

15/10/2012, 09h26
StringBuilder

Bonjour,

Merci pour votre réponse détaillée.

En effet, je pars sur SQL Server 2012.

Pour le moment, il s'agit par contre d'une ébauche, pas de l'application définitive : le SGBD tout comme certaines règles ne sont pas figés. Cependant il y a 99% de chances que ce soir SQL Server, vraisemblablement la dernière version.

Pour ce qui est du ralentissement, j'avoue qu'autant je pense assez bien maîtriser la modélisation d'un point de vue "conversion des besoins en MPD", autant en ce qui concerne le "noyau dur", c'est à dire les mécanismes internes du SGBD et leurs subtilité, j'avoue que ça ne m'intéresse pas énormément, et donc mes connaissances en restent au même degré que l'intérêt que j'y porte, même si je suis conscient que c'est très important.

Du coup, j'ai cru comprendre d'après les explications de SQLPro que la présence d'une clé alternative provoquait deux lectures afin de retrouver une ligne :
- Une première lecture dans l'index de la clé primaire, qui donne un rowid
- Une seconde lecture dans l'index interne au SGBD (donc invisible pour l'utilisateur), qui contient alors la position de la ligne
- Et enfin on va chercher la ligne

Alors que d'après lui, un index portant sur une colonne unique utilisera sa valeur comme rowid, évitant la lecture intermédiaire.

Je peux tout à fait avoir mal compris.

D'après lui, les mécanismes d'Oracle tout du moins sont différents, et fait que ce dernier peut sans problème utiliser des clés composites et même de types non entiers (varchar, etc.) sans dégradation de performances.

Ceci dit, votre piste me donne une idée : pourquoi ne pas maintenir une clé primaire de type int, plus une clé unique composée ?
Le seul hic,c'est que je vais doubler le nombre d'index (sur la PK et la clé alternative) et augmenter la taille des tables...

Bon, il va falloir que je réfléchisse à ce sujet.

Et je vais tneter d'échanger avec SQLPro sur ce sujet, vu qu'il maîtrise très bien SQL Server.

En tout cas, je vous remercie pour vos pistes !