CODEWITHSUNDEEP
relational-databaseentity-relationship
Kendel Ventonda
Kendel Ventonda

Reputation: 411

Mapping ER model to Relational model used primary key twice

I know this is a simple matter, but I find a problem that I could not find an example for to help myself. The PID is the primary key for Person

In my ER model, a "person" can be friends with another "person" through the node "befriended_with". While I can imagine how the table looks for the person, I have a problem with the "befriended_with" table. In this, the primary key PID would in turn be inserted and would have to be again hypothesized with the PID in order to connect two persons together.

Person

PID  | Name
---- | -----
01   | X
02   | Y

befriended_with

PID  | PID
---- | -----
01   | 02

But I can not imagine that this can work like this. How is this usually implemented?

Upvotes: 2

Views: 227

Answers (1)

philipxy
philipxy

Reputation: 15118

The names of columns do not matter. It is merely convenient as a mnemonic to use the same name in two different tables when the values in those columns identify things of the same sort/kind/type. (Especially when querying via NATURAL JOIN or USING.) But give columns in the the same table different names. (Although SQL allows duplicate names in query results.)

--PID identifies a person named NAME
Person(pid, name)
-- person PID has befriended FID
-- FK pid REFERENCES Person (pid)
-- FK fid REFERENCES Person (pid)
Befriended_with(pid, fid)

We declare a FK (foreign key) when the values for a column list must appear as values for another column list. Participation lines out of relationship/association diamonds in ER diagrams correspond to columns of FKs.

Upvotes: 2

Related Questions