CODEWITHSUNDEEP
database-designdatabase-schema
Cary Bondoc
Cary Bondoc

Reputation: 2988

What should I connect when creating a relationship between 2 tables?

For example I have 2 tables namely parent table and children table

Parent table

parent_id

name

Then I have Children table

children_id

name

Then a Relationship Table

relationship_id

child_id

father_id or father_name?

mother_id or mother_name?

If I want to create a relationship between 2 tables that indicates the parents of the children what should I link?

Should I create a field named for example a father_id to get the unique id of the parent or father_name to get a direct link for the name of the father in Relationship table if I want to get the name of the father?

Upvotes: 0

Views: 54

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82484

You should (and at least in some if not all databases that's your only option anyway) always use the primary keys for relationships.
At least one side of the relationship needs to be connected to the table's primary key.

However, for the example you wrote, I would go with a single person table: 

TblPerson
(
    Person_Id, -- Primary key
    Person_FirstName, 
    Person_LastName, 
    Person_Mother_Id, -- Self reference FK
    Person_Father_Id -- Self reference FK
)

Upvotes: 1

Related Questions