0

How does one implement the following relationship?

  • One car may belong to zero or many persons
  • One person may own zero or many cars

Ideally I could have a junction table that has a zero to many relationship to both Car and Person tables. Would that be the correct table organization?

  • 3
    I don't get how a regular junction/linking table doesn't do what you want. – Evan Carroll May 4 '18 at 15:32
3

For practical purposes, if you're implementing a many-to-many relationship with an intersection table - which is by far the most common way to do it, then you will in effect have a "zero or more to zero or more" relationship.

Relational databases don't have declarative referential constraints that enforce the child end of a one to many relationship. In other words, your CAR table can't have a constraint (imposed by DRI) that enforces 0, 1 or many owners. DRI works on the table with the foreign key, not the parent (referenced) table.

This means that unless you implement some additional procedural logic in your application, you wouldn't be able to enforce 1 or more, so you in effect get 0, 1 or more to each of CAR and PERSON from the intersection (OWNERSHIP).

Implementing a many-to-many relationship in this way is consistent with correct normalization.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.