Re: Database design, Keys and some other things

dawn wrote:
> 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.

I have two questions:
1. Let VIN be the primary key for the Car table. How we can enter   information in the table Car that vin1 (that is the Buick) is again   painted to the blue.
2. Let SSN be the primary key for the table Person. How we can enter

   information in the
   table Person that Mary Adams changed her name again to Mary Jones

• she got divorced. These two questions show one limitation in the RM.

>
> > 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.
>
> To the extent feasible, I would suggest modeling real world entities as
> whole at the outset. A person in the real world would be modeled by a
> row in the Person table. A Car in the real world would be modeled by a
> row in the Car table. Feel free at the start of your modeling to add
> properties that are lists to an entity (car colors, for example).
>
> Then you can move to 1NF if needed for the target implementation. A
> Car might be referenced in multiple CarColor rows after you split out
> the list properties. Similarly, a Person might have a list of former
> names, but stay the same person. So, you do not want two PersonKeys
> for this one person.

I built this model so that the relations are at lest in the BC normal form automatically. There is only one candidate key, there are no compound keys and all the other values that are not Key can be repeated any number of times. If this reason (generally speaking) is not good please let me know.

>
> 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 Sun Sep 25 2005 - 04:14:44 CEST