10

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.

Hammerite
  • 21,755
  • 6
  • 70
  • 91
  • 2
    this may only be an example - but here i would not restrict that because of the fact that a showroom is associated to the manufacturer, then all cars sold there must be made by that manufacturer... further - the same salesperson may work at multiple showrooms. – Randy Sep 05 '12 at 19:49
  • ^^ What does the showroom have to do with the manufacturer? – Kermit Sep 05 '12 at 19:51
  • I have clarified the question. A Showroom only sells cars made by the Manufacturer to which it is affiliated. – Hammerite Sep 05 '12 at 19:52
  • i would enforce this in trigger logic. – Randy Sep 05 '12 at 19:56
  • Back to the old drawing board: Restriction R: if two items **must be the same**, (at least) **one of them is redundant**. – wildplasser Sep 05 '12 at 19:59
  • wildplasser, perhaps you could elaborate on your comment and explain which foreign key relationships you feel should exist and how Restriction R should be enforced. How do I know, of a car, both what Model it is and which Salesperson sold it without the redundancy you identify? – Hammerite Sep 05 '12 at 20:09
  • What I meant to say: if you *know* that two items are the same, you only need to store one of them. It appears your current data model contains such a redundancy, and the only purpose of your restriction R is to maintain that redundancy. – wildplasser Sep 05 '12 at 20:13
  • 1
    Well, I can only repeat my request for you to explain further. Which piece of information is redundant? If I have a Car, then I need to know which Model it is, and I need to know who sold it. If I have a Model, I need to know who makes it (and I need to know this even if there are not yet any Cars of that model). If I have a Salesperson, I need to know where they work, and I need to know which Manufacturer the workplace is affiliated with (even if it has not yet sold any Cars). – Hammerite Sep 05 '12 at 20:18

2 Answers2

4

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.

Community
  • 1
  • 1
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
3

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

enter image description here

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)
;
Damir Sudarevic
  • 21,891
  • 3
  • 47
  • 71
  • Thank you for your answer. The key observation appears to be the creation of the cross-reference table CarDisplay, and the alteration of foreign key relationships to accommodate it. – Hammerite Sep 05 '12 at 21:18
  • @Hammerite, also note alternate key (unique index) `AK` on SalesPerson, FK from `Sale` table points to that key. – Damir Sudarevic Sep 05 '12 at 22:15