CODEWITHSUNDEEP
databasedatabase-designnormalization
ColdAndTired
ColdAndTired

Reputation: 3

Database design - defining a basic many-to-one relationship

This is a basic database design question. I want a table (or multiple tables) defining relationships between customers. I want it so PrimaryCustomer can be linked to multiple SecondaryCustomers, and can have many SecondaryCustomers with the same relationship.

PrimaryCustomerID  RelationshipID  SecondaryCustomerID

1) If the primary key is {PrimaryCustomerID} then I can only have one linked customer of any kind.

2) If the primary key is {PrimaryCustomerID, RelationshipID}, then I can only have one linked customer for each relationship type.

3) If the primary key is {PrimaryCustomerID, RelationshipID, SecondaryCustomerID}, then I can have whatever I like, but having all columns as the primary key seems completely wrong.

What's the right way to set things up?

Upvotes: 0

Views: 519

Answers (4)

Damir Sudarevic
Damir Sudarevic

Reputation: 22187

Nothing wrong with No 3.

alt text

If you need to prevent reverse-relationship duplicates, you can use

ALTER TABLE CustomerRelationship 
ADD CONSTRAINT chk_id CHECK (PrimaryCustomerId < SecondaryCustomerId);

Upvotes: 0

nvogel
nvogel

Reputation: 25526

A third alternative might be for the key to be (PrimaryCustomerId, SecondaryCustomerId), which would make sense if only one type of relationship is permitted per pair of customers. What keys to implement should be defined by what dependencies you need to represent in the table so that the table accurately represents the reality you are modelling. There's nothing wrong in principle with compound keys or all-key tables.

Upvotes: 1

tawman
tawman

Reputation: 1

If a customer can only be linked to one primary customer then you can use a simple recursive relationship in the customer table itself.

  • CustomerID as PK
  • PrimaryCustomerID as FK to CustomerID

Upvotes: 0

HLGEM
HLGEM

Reputation: 96552

Number 3 is the right way to go for this data model. Linking tables often have all the columns in a join as all they do is link to other tables.

Upvotes: 0

Related Questions