6

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.

  • 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 '14 at 7:32
  • 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 '14 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 '14 at 9:52
7

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.

  • 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 '14 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 '14 at 10:59
0

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.

0

enter image description here

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

  • 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 '15 at 20:36
-1

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]

  • 1
    Welcome dlink. Try to provide thorough answers that are more than just a link otherwise they may be removed. – LowlyDBA Feb 4 '15 at 21:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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