Re: Database design, Keys and some other things
Date: 24 Sep 2005 16:58:32 -0700
Message-ID: <1127606312.230585.4250_at_g14g2000cwa.googlegroups.com>
David Cressey wrote:
> "vldm10" <vldm10_at_yahoo.com> wrote in message
> news: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
> >
>
> You might want to compare your approach to that of Ralph Kimball. In one of
I didn't read any book from Ralph Kimball. If he completed a theory about this then it is good. If there is some new explanation what is the key, how we should construct the Conceptual Model and all other things, then we have new Data Model. If his suggestion is with old definition of key then it can cause some inconsistency.
> his books on Data Warehousing, he suggests that it is better to use inserts
> only on dimension tables. Thus whenever, in the normal course of events, an
> attribute takes on a new value, and in an "ordinary" database we would
If you noticed in my example it is not related to new value of attribute, rather it is related to event. It can be the "closing" of data, i.e. there is no new attribute value. It can be 5 same rows. Only the key's values are different, i.e. again there is no a new value of attribute.
> update an existing row, Kimball suggests instead to insert a new row.
>
> This new row will have a new surrogate key, but the same natural key as a
> pre existing row. Facts that already reference the prior dimension row
> will continue to do so. New facts that are added later will somehow be
> linked to the newer row. Perhaps a date field, as has already been
> suggested by Masterdam, will be needed for this purpose.
>
> It seems to me that there are numerous points of similarity between
> Kimball's method and yours.
Received on Sun Sep 25 2005 - 01:58:32 CEST