Foreign key on a logical data model?

Foreign key on a logical data model?

I often get into passionate discussions with data modelers on whether a logical data model (LDM) should contain foreign keys. Those that believe an LDM should not include foreign keys explain foreign keys are relational database constructs and therefore should appear on the physical only. Those that show foreign keys explain that foreign keys often increase model readability as it is easier to see how entities relate to each other, and for other reasons as well. What are your thoughts? Do you show foreign keys on a logical? What are your reasons?


  1. Gary Guttman 5 years ago

    Yes, I always show them on my logical model or else it would be very difficult to see how the entities relate. Yes, they are instantiated in the physical model but without showing them it is very difficult to explain the logical model to non-technical people. I don’t show other physical implementation attributes, such as, User_Id or Update_Timestamp in my logical model but only add them to the physical model as physical only attributes.

  2. Win Wheeler 5 years ago

    My physical data model often reflects the reality of a legacy database, including entities that should have referential integrity but don’t because of needed data cleanup.

    My logical model shows referential integrity. It is a statement of vision — a goal to be achieved. With sufficient resources the physical model (and database) will become more like the logical.

    When creating an entirely new design, I still include referential integrity in the logical model as partial basis for a data quality conversation.

  3. Michael Brackett 5 years ago

    Primary keys must be included on a logical data model for that model to be complete. A complete logical data model consists of formal data names, comprehensive data definitions, the complete data structure, and formal data integrity rules. Many logical data models produced today are grossly incomplete. An incomplete logical data model substantially compromises business professional’s understanding of the data needed to support the business. One excuse often given for not including foreign keys is the space on a diagram, which is totally inappropriate. The diagram is only one part of a complete logical data model. Other parts are a list of the data attributes in a data entity and the roles they play-which is primary and foreign keys, and the comprehensive data definitions. That’s the complete data model that all professional data modelers should develop and present to the business.
    To go a step farther, the documentation of the relations between data entities is done through the foreign keys, at least in modern documentation techniques. Therefore, the foreign keys must be displayed to support the documentation of those relations.
    Remember, the primary purpose of a logical data models is to provide a complete understanding of the data to the business professionals. Do do otherwise shows a lack of professionalism on the part of the data modeler.

  4. Ana Ristic 5 years ago

    My logical data models always include foreign keys because I believe that it improves readability of the diagram and shows the relationships that exist among tables/objects.
    Even with legacy systems which usually don’t have FK constraints defined in the physical schema, if I can be certain about which data elements play the role of a foreign key in the table I mark them as such on the logical model. Sometimes, if it proves helpful to business users to understand the model better , I include natural/business keys in the logical model because they are user facing identifiers (codes) and users are for the most part already familiar with these identifiers while they may not be familiar with foreign key identifiers. This greatly helps with telling the story that data model is supposed to convey.

  5. Yvonne Martin 5 years ago

    Most comments appear to be neglecting the fact that the foreign keys come about from the relationships. Therefore it is the relationships and well constructed relationship names that provide clarity to the model. By placing FK names in child entities they become unnecessarily busy. The only notation that gets it right in my opinion is Barker notation.

  6. Terry Mason 5 years ago

    I fail to understand how one can go beyond 1st Normal Form in a logical data model without showing foreign key attributes. Once the point is reached where “every attribute within an entity depends on the key and nothing but the key”, relationships will most likely have emerged, along with the associated foreign keys. Therefore, to not show the foreign keys, would render the model incomplete and without rigor.

    It could be however, that people are thinking about surrogate keys which, be they primary or foreign, really have no place in a logical data model anyway. Surrogate keys are sometimes loosely referred to as “technical keys”, which is the give away that they actually belong in the physical data model.

  7. Pam Rivera 5 years ago

    Relationships are visible in the logical – it the R in ER diagram. This is where we document the business rules around the data. I can’t understand why we wouldn’t think it belongs in a logical model.

  8. Gordon Everest 5 years ago

    I would NEVER show foreign keys in a logical data model. I prefer to call it a business data model because it is of and for the business, the people in the business, not for the machine and not (primarily) for implementation in a (relational) DBMS. Since all data models are “logical” I also prefer to drop the adjective – all data models are logical in the sense that they are developed according to a modeling scheme, a logical set of rules for building a data model.
    .. Foreign keys and relationship arcs are completely redundant in a data model diagram (they should be completely consistent). Since arcs are visually much easier for users (viewers of the model diagram) to comprehend, they should be the preferred method of representing relationships in a diagram. Besides, if you insist on using foreign keys (and enforcing 1NF, that is, not allowing multivalued attributes) then you are limited to at most 1:Many relationships (since you cannot have a multivalued foreign key). In a relational database, all M:N relationships must be resolved to two 1:Many relationships onto an intersection entity. Users are perfectly able to comprehend a Many-to-Many relationship. The relationship arc can be drawn with a fork at each end:
    [ X | …. ]>——-<[ Y | … ]
    to represent a M:N relationship between X and Y. Of course, if there are attributes on the relationship (attributed relationship), that is, there are other data items which are "determined" by [ X-Y ] (i.e, functionally "dependent" on X-Y ) then it is necessary to realize the intersection entity. It may have real meaning in the user domain.
    .. Some argue that a logical data model is incomplete without including foreign keys. That is false. But you must properly and fully represent the relationships using arcs in the model diagram, including the exclusivity (at most one, or many) and dependency (at least one, or none, optional) integrity constraints. Note that referential integrity is nothing more than a dependency constraint in a relationship (the entity which would contain the foreign key, is dependent on the entity to which the foreign key is "pointing").
    .. Foreign keys are for physical implementation in a relational (SQL) DBMS and have no place in the "logical" business data model. They are for the system (RDBMS) not for people. A relational database needs foreign keys because they can't "store" arcs, but arcs communicate semantics better and more readily for people. We draw data model diagrams for people not for machines!

  9. Mike 5 years ago

    Yes I add FK to my LDM, It helps me quickly see the relationships between entities. It also really helps the DBA and development group. Even though as Data Molders we don’t get the credit for what we do because for a long while DBA/Programmers and a list of other titles think they can do what we do and better and it gives them more job security. In my last positions besides the definitions fir the LDM we put in the datatypes and lengths, this saves the project tons of time and DBA’s job is made a lot easier.

  10. Ross 4 years ago

    Always show them. The relationship is a piece of data that is shared between two entities, whether you show it or not the structure is the same. You can determine its existence by implication but why not be explicit and reduce the opportunity for misinterpretation by one of the parties involved in model reviews. If you were to change, or support multiple, physical implementations in relational/non-relational data stores then your logical model can be transformed into both provided you have the foreign keys defined.

  11. Karel van der Walt 1 year ago

    It all depends on the delineation between Conceptual Logical and Physical Models:

    Conceptual is agnostic of ‘shape’ of the extension. Conceptual can be one of ER, EER, ORM,…

    For Logical we already commit to the shape of the extensions e.g. tabular/relational, key-value, document (JSON, XML), Graph,…

    For Physical we commit to a vendor’s implementation of the Extension e.g. PostgreSQL for relational.

    References is a relational concept and will be present in a Relational Logical Model.
    Foreign keys commit references to UPDATE|RESTRICT semantics on UPDATE|DELETE and should be optional but available

Leave a reply

Your email address will not be published.