How to represent foreign key in an ER diagram

database-designdatabase-diagrams

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.

Best Answer

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.

Related Solutions

Modelling Relationships in an E-R Diagram, Table or Foreign Key

Your first option works best when there is the possibility that a lecture can have multiple tutors or when a tutor can teach multiple classes. Your second option works best when you can guarantee that a class will only ever have a single tutor at any one time (otherwise you would need multiple entries in your class table to accommodate extra tutors). The third one will only work if you can guarantee that a tutor will only ever teach one class (the opposite of the second scenario).

If you want/need maximum flexibility then go with option 1. However, you should choose the option that best suits your needs based on the descriptions of the types I have given above.

You can indeed over-normalize a database, and in some cases a de-normalized structure may suit the requirement better - but I don't think this is one of those times.

I hope this helps you.

Sql-server – How to enforce a nullable foreign key

Yes, why not? Did you try it?

CREATE TABLE dbo.foo(fooid INT PRIMARY KEY);

CREATE TABLE dbo.bar(barid INT PRIMARY KEY,
  fooid INT NULL FOREIGN KEY REFERENCES dbo.foo(fooid));

INSERT dbo.foo SELECT 1;
INSERT dbo.bar SELECT 1,1;
INSERT dbo.bar SELECT 2,NULL; -- succeeds
GO
INSERT dbo.bar SELECT 3,2; -- fails

If this is not what you meant, then please elaborate or re-word your question.

Best Answer

Related Solutions

Modelling Relationships in an E-R Diagram, Table or Foreign Key

Sql-server – How to enforce a nullable foreign key

Related Question