[mso] Re: Database Integrity


============================
How can I protect the
data integrity (i.e., relationship between vehicles/milesdriven/activity)
but use the SAME NUMBER to identify a different vehicle?
============================

Actually this is a very good, and general, data modeling question.

It is not specific to Access or DB2 or Oracle or IDMS or IMS or Model 204
(ok, I'm dating myself here), but is relevant to all of them, and more.


Let me give you a similar, real world question.

I am James Stuart Huggins. But I am not the only James Stuart Huggins in the
world. If you were building a database to contain people, how would you
distinguish me, this particular James Stuart Huggins from some other James
Stuart Huggins.

The answer is that each entity represented in the database must (and that is
a capital "M" must) have a unique primary key. In America, the answer
frequently chosen to distinguish people in such databases is just such an
ID: a student ID, and employee ID or the (unfortunately) ubiquitous Social
Security Number.

In your case, you must also find or create such a number. Here is a
candidate I would suggest: the VIN: Vehicle Identification Number. It is
assigned by the manufacturer and is unique. Your car has one, for example.
And your state department of motor vehicles uses it to identify your
particular car from the several hundred that are the same make, model,
color, year, etc.


In your case, the district assigned number (DAN) would be a secondary key.

If you are keeping history, you might also need to impose business rules.
For example, you might have two vehicles numbered 79: one the current 79 and
the other the 79 that was replaced. A business rule that you might impose
would be that a duplicate DAN (e.g., 79) can't be created until the old DAN
(again, the 79) has a retired date in its record. That is, while your
database could support multiple/duplicate DANs, only one of them could be
"active".


James S. Huggins




...

*************************************************************
PLEASE READ!!!!

This is not S*P*A*M!  You are receiving this mail because you either subscribed 
to mso@xxxxxxxxxxxxx or to it's earlier version, 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To Unsubscribe from this group, send an email to mso-request@xxxxxxxxxxxxx with 
a subject line that says "unsubscribe" (without the quotes).  Do not put 
unsubscribe IN CAPS.  Screaming doesn't get you out any faster and the caps 
prevent the function from working.

To change your email settings to digest or vacation (no mail), visit the 
group's homepage for full instructions.

http://www.freelists.org/webpage/mso
*************************************************************

Other related posts: