CODEWITHSUNDEEP
mysqldatabase-design
user1493428
user1493428

Reputation: 79

MySQL: Adding connections between people

I have a table "People" (ID, Name)

1, George
2, Nick
3, John
4, Paul

Each customer can be connected to another customer (just for reference) For example George is connected with Paul

So I create a new table PeopleConnections (PeopleConnectID, PeopleID, ConnectedID)

and I add the relations between people

1, 1, 4 (George with Paul)
1, 2, 3 (Nick with John)
1, 2, 4 (Nick with Paul)

I need to display all relations inside the record view gui. So

Is that the correct design to handle this? What foreign keys should I add?

Upvotes: 0

Views: 41

Answers (2)

Esoteric Screen Name
Esoteric Screen Name

Reputation: 6112

Yes, your design is generally appropriate. You should place foreign key constraints on PeopleConnections.PeopleID and PeopleConnections.ConnectedID. Both of those should reference People.PeopleID.

You may also wish to place a unique constraint on the aggregate of PeopleConnections.PeopleID and PeopleConnections.ConnectedID, to prevent duplicate relations.

Upvotes: 0

DaGLiMiOuX
DaGLiMiOuX

Reputation: 889

It's a many-to-many relationship and it's a correct design, in my opinion. Your foreign keys should be your ID from People table. You already answered yourself in that part.

Upvotes: 1

Related Questions