CODEWITHSUNDEEP
mysqldatabasedatabase-designrelational-database
kenshin9
kenshin9

Reputation: 2365

Database Relationships - One-to-One that also has One-to-Many

Let's say you have one cook that has one restaurant, and vice versa. So with a one-to-one relationship, you would have the primary key id in the cooks table and cook_id as the primary and foreign key in the restaurants table.

So how would you represent a relationship of one-to-many between the restaurant and its customers? Since the restaurant does not have its own ID, would the customers table have its own id and then contain foreign keys of cook_id?

Edit: I've thought of a better and more realistic example. Let's say you have a work order that only ever has one quote. You'll have the work order's id in the quotes table, since it's 1-to-1. Being a quote, it's bound to change, and that same particular quote gets revised. If you wanted to record the revisions made to a quote (or some sort of history log), you'd want something like a quote_revisions table. In this case, a work order only ever has one quote, and a quote can have many quote revisions. With what IDs do you link the quotes and quotes_revisions table?

Upvotes: 0

Views: 216

Answers (3)

Rick James
Rick James

Reputation: 142298

Using the Work_order example:

  • Work_order would have a PK of, say, wo_id, and it might be AUTO_INCREMENT.
  • Quotes would have a PK with the same wo_id, but not AUTO_INCREMENT.
  • Quote_revisions would have an INDEX(wo_id), but some other column(s) for the PK.

Work_order and Quotes are "1:1", as provided by wo_id.

Quotes and Quote_revisions are "1:N"; wo_id in both tables provides that relationship.

It is rarely useful to have 1:1, but your example might be a good use case. (One table is relatively large and static, the other is relatively small and frequently changed.)

Upvotes: 1

reaanb
reaanb

Reputation: 10065

Since you have a one-to-one relationship, the cook's id is the restaurant's id too. You can relate customers to restaurants by associating customer keys with cook/restaurant keys in a table (customers or another table). The one-to-many cardinality is enforced by placing a unique constraint on the customer's key so that they can't be associated with more than one restaurant/cook.

Upvotes: 2

Ken Clubok
Ken Clubok

Reputation: 1238

I would instead have a restaurant_id field as the primary key in the restaurant table, along with cook_id as a foreign key. Yes, this structure would support a one-to-many relationship just as well as a one-to-one relationship, but I believe each entity should nevertheless have its own ID. If you like, you can put a unique constraint on the foreign key, to ensure that the relationship does remain one-to-one. Or you could simply have a single restaurant table that includes fields with information about its head chef.

Upvotes: 0

Related Questions