How to represent foreign key in an ER diagram?
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. https://www.lucidchart.com/pages/ER-diagram-symbols-and-meaning. Please tell me how I can do it.
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
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?
I prefer this format, using "crows feet" to illustrate the many-to-one joins
When drawing ER diagrams, I have used the following graphical convention: Label the relationship lines with the foreign key column name(s), like so:
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.
This page, by LucidCharts has a nice write up on how ERD should look.
Under Physical ERD Symbols, it shows how to represent keys.