CODEWITHSUNDEEP
sqlforeign-keysentity-relationship
Floran Gmehlin
Floran Gmehlin

Reputation: 854

SQL : ER Model, Foreign keys

I have a simple question, but I am still confused about it.

Whenever you design an ER model, how are you supposed to draw PK's and FK's ?

Let's say I have two tables, User and Country

User
ID | firstName | lastName | FKCountry
1  | John      | Stewart  | 1
2  | Paul      | Duschmol | 2
etc.

Country
ID | country     | code
1  | Germany     | GER
2  | Switzerland | CH
etc...

Well, should I add the column "FKCountry" in the ER model or not ? I mean, there will be the relation (Rhombus) between both entities, but should I explicitly add the "FKCountry" by the User entity ?

Hope my question is clear, Thank you in advance :)

Upvotes: 0

Views: 1099

Answers (2)

woemler
woemler

Reputation: 7169

Yes, you should add FKCountry to the User table in your ER diagram. If the column exists in your table, it should be represented in the entity. There should be zero doubt that the relational marker connects Country.ID to User.FKCountry. Just because it is obvious in this example does not mean that in other relationships, the linked columns might not be named completely differently and not obvious.

Upvotes: 1

John Woo
John Woo

Reputation: 263723

The primary purpose of Foreign Key is to enforce referential integrity between the two tables.

If the relationship of Country to a User is One-to-Many then it should be fine for adding column FKCountry on table User.

However, if the relationship is Many-to-Many in which Country can have many User as well as User belongs to multiple Country, then a 3-table design is much preferred. Wherein the 3rd table is the association table between the other two.

Upvotes: 2

Related Questions