Is it correct to represent a foreign key on a conceptual data model, or should I only introduce foreign keys when I am doing the relational model?
A:
Foreign keys are entirely (and exclusively) part of the relational model.
Object models don't rely on foreign keys; they have direct object references not based on keys.
S.Lott
2010-04-29 10:02:26
The OP queried about the conceptual model, not an object model.
mdma
2010-05-01 17:25:03
@mdma: What did the OP mean by "conceptual"? Do you have a definition that the OP would agree to?
S.Lott
2010-05-04 11:27:32
I would say that the conceptual model is the simplest model that captures the essence of the problem. An object model (or relational model) is in logical model territory. Here's a simple comparison of conceptual, logical and physical models. http://www.1keydata.com/datawarehousing/data-modeling-levels.html
mdma
2010-05-04 11:51:16
+2
A:
Experts are divided on this one. Here's my opinion.
The classic ER (Entity-Relationship) model identifies relationships, but does not implement them as foreign keys. In an ER diagram, relationships appear as lines between boxes. Extra marks classify them as many-many or many-one, and as mandatory or optional. A relational model always uses foreign keys to identify and implement relationships. A relational model diagram will use lines between boxes for visual purposes, but a complete attribute list will include these foreign keys.
Another difference between ER modeling and relational modeling is that in relational modeling, a many-many relationship will be diagrammed as a box with two (or more) lines leading up to it. In ER modeling, a binary many-many relationship is modeled without a box of its own.
I always used to construct the conceptual model using ER modeling, and leave the foreign keys out. Then, at logical design time, I would switch over to the relational model and plug in the foreign keys. At this time, many-many relationships would acquire extra boxes of their own, to hold a pair (or a tuple) of foreign keys.
Many experts don't follow my practice. They opine that the ER model is superfluous for conceptual modeling, and that it's best to begin with a relational model at the conceptual level, while you are still doing the requirements analysis. Then you don't have to switch modeling conventions in mid stream. From a theoretical point of view they are right.
Where the ER model has served me well is in explaining the model to unsophisticated clients. Some of them have a quicker intuitive grasp of the ER model than of the relational model.
Walter Mitty
2010-04-29 10:15:21
+1 I agree with Walter - conceptual model is not normally representing detailed design - this is what physical model is for. Conceptual model's goal is to describe general break down in objects and what are the relationships - the design of foreign key is not important or you would go right to physical model and skip conceptual model
IMHO
2010-04-29 15:03:17
Excellent comment. You clarify the difference between analysis and design. I was hinting at that, but you laid it out.
Walter Mitty
2010-04-30 02:49:45