19

Suppose I have a 'Transactions' table which has a column 'Customer ID' (Foreign Key) and a Customer Table having 'ID' (Primary key). How do I show the relation between the two tables and showing that the 'Customer ID' is the foreign key of 'Transactions' Table which is the primary key in the 'Customer' table?

I googled this question and also checked this forum for my query but couldn't find an exact example with a diagram addressing my question.

Please explain me, if possible, with a diagram.

Improve this question
4
  • I found the following link which says that we can show a Foreign Key only in a conceptual ER Diagram. lucidchart.com/pages/ER-diagram-symbols-and-meaning. Please tell me how I can do it.
    – MK Singh
    Sep 15, 2014 at 7:32
  • 2
    I've learned in school(!!) that primary keys can be represented with a straight underline under the attribute(s) and foreign key(s) with a dotted line
    – user1363989
    Sep 15, 2014 at 9:50
  • Okay. But how do I show that the foreign key in one table is the same thing as the primary key of another table whose names are different in their respective tables (as asked in the example in the question about 'ID' and 'Customer ID')?
    – MK Singh
    Sep 15, 2014 at 9:52
  • As this question is referring to a „No-Chen“ notation (because it asks for showing fk relations), I have opened a new question explicitly for an ER Model in Chen notation at dba.stackexchange.com/questions/271264/…
    – questionto42
    Jul 21, 2020 at 8:46

5 Answers 5

Reset to default
20

ER Diagrams were originally used only to represent the ER model. The ER model does not use foreign keys to represent relationships. It uses lines between boxes. The lines have some kind of indicator for cardinality at either end or both ends. Sometimes, a relationship will be indicated separately by a diamond.

Today, more than half of the ER diagrams floating around are really diagrams of a relational model, and not of an ER model. A relational model has the foreign keys included in the tables, and these serve to implement the relationships which the ER model identifies. And a relational model will have an extra table, often called a "junction table" between two entity tables that are linked by a many-to-many relationship. This junction table contains two or more foreign keys.

There are many ways to represent a relational model. Perhaps the simplest is the "Relationship Diagram" that MS Access can produce from a completed database. This will be fairly complete, if the database builder has identified the foreign keys.

There are many tools that are more sophisticated than MS Access for making diagrams on a larger scale. Some of these are used before building the database. Some are used after.

Improve this answer
2
  • Currently I am just making a diagram on a paper. Can you please show how the above two tables (in the question) can be depicted in a relationship diagram?
    – MK Singh
    Sep 15, 2014 at 10:01
  • If you will search on "ER Diagram Customers Transactions" you will get a lot of images that show various ways to do what you want.
    – Walter Mitty
    Sep 15, 2014 at 10:59
4

When drawing ER diagrams, I have used the following graphical convention: Label the relationship lines with the foreign key column name(s), like so:

Example ERD

This makes it clear which column in the child table is the foreign key to the parent table. Indicating primary key status can be done by underlining the attribute in question.

What may be more useful than this is a naming convention that makes it clear what is the primary key of a table (easily done if you use surrogate keys by convention) and what is a foreign key column.

Some relational model diagrams also include a key participation label to the left of the column names in the list of columns (e.g. "PK", "FK1", "FK2",...) which can help especially if you have composite keys.

Improve this answer
3
  • Is this convention also published somewhere, or is it just your (good) idea?
    – questionto42
    Jul 21, 2020 at 8:56
  • @Lorenz it's my adaptation for a physical entity-relationship diagram of the version of crow's foot notation (en.wikipedia.org/wiki/…) published by James Martin (en.wikipedia.org/wiki/James_Martin_(author) ) in his book Information Engineering which used the relationship name as the label on relationships. To me it's a sensible extension from the logical model to the physical model.
    – Joel Brown
    Jul 21, 2020 at 10:34
  • Even if it helps, it stays a private idea then, this was not totally clear from the expression “I have used”.
    – questionto42
    Jul 21, 2020 at 17:24
4

enter image description here

I prefer this format, using "crows feet" to illustrate the many-to-one joins

Improve this answer
2
  • I like crows feet for ER diagrams as well. For relational diagrams, I prefer the arrowhead. note that a crows foot goes on the "many" end of a line, while an arrowhead goes on the "one" end of a line.
    – Walter Mitty
    Nov 7, 2015 at 20:36
  • There are only the FK / PK symbols missing at the line ends in this graph here :) Still, this follow-up answer to dlink's first posting of the link got too few votes in my opinion. dlink's previously posted link was criticised, it was already mentioned in the first comment of the question before by @MKSingh who had asked ;), BUT! it seems that the link is better than stated there. If you watch the video that is offered under "physical ERD Symbols", it shows exactly how to represent multiple FK in a an ERD, here is the direct link, go to 7:28, youtube.com/watch?v=QpdhBUYk7Kk
    – questionto42
    Jul 19, 2020 at 10:42
3

Many years later, please mind the different notations of ER diagrams, the question is thus not precise enough.

Have a look at all answers at

https://stackoverflow.com/questions/48191228/is-erd-considered-a-kind-of-uml-diagram#48198083

and

Which one is an ER diagram?. One answer here even states that the Chen notation is the ERD, while Crow Foot notation is said to be an EAD (Entity Attribute Diagram), see @JoshuaGuttman.

The Chen notation does not show explicitly the FK relations, as the accepted answer already explains, while other notations can do this.

If you take for example the Crow Foot or Baker's notation instead, you might take the "most precise" answer of @dlink where the lines lead exactly to the FKs. Then it is still unclear what to do with composite FKs leading to composite PKs, and probably because of that, professional programs simply do not graphically link the line endings with the FKs. Then, naming is all you can refer to, which is enough anyway if you keep strictly to a naming convention (like CustomerID to Customer.ID, or other rules). See for example Visual Paradigm at https://www.visual-paradigm.com/guide/data-modeling/what-is-entity-relationship-diagram/

vp erd

Improve this answer
0
0

This page, by LucidCharts has a nice write up on how ERD should look.

Under Physical ERD Symbols, it shows how to represent keys.

[https://www.lucidchart.com/pages/ER-diagram-symbols-and-meaning]

Improve this answer
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.