Database design, Keys and some other things

From: vldm10 <vldm10_at_yahoo.com>
Date: 23 Sep 2005 06:36:41 -0700
Message-ID: <1127482601.079853.35050_at_f14g2000cwb.googlegroups.com>



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      ...

...

In the Car table VIN = Vehicle Identification Number

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

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.

Vladimir Odrljin Received on Fri Sep 23 2005 - 15:36:41 CEST

Original text of this message