Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

database - How to keep foreign key relations consistent in a "diamond-shaped" system of relationships

Consider this situation: a Car is bought from a Salesperson. A Salesperson works at a Showroom (and at only one Showroom). A Showroom is affiliated to a Manufacturer, and only sells cars made by that Manufacturer. At the same time, a Car is of a particular Model, and a Model is made by a Manufacturer.

Restriction R: A Car's Model's Manufacturer must be the same Manufacturer as the Car's Salesperson's Showroom's affiliated Manufacturer.

The diagram shows the obvious foreign key relationships.

     ---->  Manufacturer  <----
     |                        |
     |                        |
 Showroom                     |
     ^                        |
     |                      Model
     |                        ^
Salesperson                   |
     ^                        |
     |                        |
     ---------  Car  ----------

How do you enforce Restriction R? You could add a foreign key relationship Car --> Manufacturer. Yet the Manufacturer of a Car can be established by joining tables one way or another around the "diamond", so surely to do this would not be normalised? And yet I do not know otherwise how to enforce the constraint.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

The way to ensure that that the "bottom" of the diamond cannot reference "sides" of the diamond that ultimately lead to a different "top" of the diamond, is to use identifying relationships and the resulting "fat" natural keys, so they can be merged at the bottom:

enter image description here

(Only PK fields shown, for brevity. You'll almost certainly want a vehicle identification number as an alternate key in Car etc...)

The ManufacturerId has been migrated down both diamond sides and eventually merged at the bottom into a single field. The very fact that it is the single filed ensures there cannot be two manufacturers leading to the same car.

BTW, this still doesn't prevent you from using surrogate keys (in addition to these naturals), assuming DBMS supports FKs to alternate keys:

enter image description here

Surrogates are redundant in this model taken alone, but you might have some other entities there that you have not shown us, which may benefit from using slimmer FKs.


The above is the most direct conversion of your diagram, where a car exists only as a sold car. However, I suspect you'd want to be able to store cars that have not been sold yet, and when they are sold memorize the car buyer etc...

So, a more complete model would look something like this:

enter image description here

We just rinse-and-repeat the identifying relationships trick, so a car cannot be displayed in a showroom of a different manufacturer and cannot be sold by a salesperson from a different showroom.

A car is unsold when there is only a row in Car. A car is sold when there is a row in Car and a corresponding row in Sale. Both Car and Sale share the same PK and this is a "1 to 0..1" relationship, which could also be modeled by merging Car and Sale, and making sale's fields NULL-able, with the appropriate CHECK to ensure they cannot be "partially NULL".

BTW, whenever you are selling something, you need to make sure the sale is "frozen in time". For example, the price actually paid by a buyer shouldn't change just because car's price changed after the sale. Take a look here for more info.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...