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

Question

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.

Answer 1

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:

(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:

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:

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.

Answer 2

If I understood the question correctly, this should be close.

Here are few details for keys

--
-- Keys for SalesPerson
--
alter table SalesPerson
  add constraint PK_salesperson primary key (PersonID)

, add constraint AK1_salesperson unique (ManufacturerID, ShowRoomNo, PersonID) 

, add constraint FK1_salesperson foreign key (PersonID)
                           references Person (PersonID)

, add constraint FK2_salesperson foreign key (ManufacturerID, ShowRoomNo)
                         references ShowRoom (ManufacturerID, ShowRoomNo)
;

--
-- keys for Sale table
--
alter table Sale
  add constraint PK_sale primary key (SaleID)

, add constraint FK1_sale foreign key (BuyerID)
                    references Person (PersonID)

, add constraint FK2_sale foreign key (ManufacturerID, ModelName, ShowRoomNo)
                references CarDisplay (ManufacturerID, ModelName, ShowRoomNo)

, add constraint FK3_sale foreign key (ManufacturerID, ShowRoomNo, SalesPersonID)
               references SalesPerson (ManufacturerID, ShowRoomNo, PersonID)
;