Re: Database design, Keys and some other things
Date: 24 Sep 2005 09:36:16 -0700
Message-ID: <1127579776.281940.50420_at_g49g2000cwa.googlegroups.com>
vldm10 wrote:
> Here is a simple example about two entities and one relationship - Car,
> Person and Owner. It tries to describe more realistically Real World
> situations. A car had its color changed twice. In the Real World
> attributes are often changed. In another entity, the person Mary
> changed her last name because she got married. In the relationship
> Owner it can happen that one person buys the same car twice during a
> period of time. It can also happen that two entities can be in the same
> relationship many times. So,I believe that this new approach is more
> appropriate.
>
> Given the table Car :
> CarKey CarID Make Color ...
> ______________________________________________________
> ...
> 23 vin1 Buick silver ...
> 24 vin1 Buick blue ...
> 25 vin1 Buick red ...
> 26 vin2 Honda silver ...
> 27 vin3 Ford black ...
> ...
You named this table "Car" which might suggest that a row in this table is-a car and that no two rows are the same car. That is not the case with your table here. If you are going to model Cars, then model Cars.
> In the Car table VIN = Vehicle Identification Number
That sounds like a candidate key for a Car table.
> Now, let Person be the following table:
> PersonKey PersonID PersonName ...
> _______________________________________________________
> ...
> 208 ssn1 Mary Jones ...
> 209 ssn1 Mary Adams ...
> 210 ssn2 John Stewart ...
> ...
>
> In the Person table SSN = Social Security Number
That sounds like a candidate key for a table of USA people. Yet, you have multiple rows for the same person. If you are going to model a Person, then model a Person.
> Then the relationship Owner, which is the act of owning a car, can have
> the following
> values:
> OwnerKey Person Key CarKey Year ...
> ___________________________________________
> ...
> 54 210 26 2003 ...
> 55 210 24 2004 ...
> 56 210 26 2005 ...
> ...
>
> More details about this example can be found on my website
> www.dbdesign10.com
> where I gave a new definition of Key and tried to develop a new Data
> Model.
> Any feedback is greatly appreciated.
This design issue might have a real name, but I think of it as doing a "1NF in place" rather than splitting out new tables for list properties. You start with a car and then you realize it has at least one multivalued property, so you distort your Car table to a CarProperty relationship table, losing the model for Car in the process. --dawn
> Vladimir Odrljin
Received on Sat Sep 24 2005 - 18:36:16 CEST