> > This is also true, but may actually cause you to require an
> > extra step, as you might have the requirement to prevent duplicate
> > values in your natural primary key, even if it's not being
> > officially used as a primary key. For example, you wouldn't
> > want to have duplicate entries with the same first name, last
> > name and SSN, even if you're using an identity column as a
> > surrogate key. Again, I'm willing to live with that, and
> > heartily recommend using identity columns, or whatever surrogate
> > key mechanism the database offers.
>
> Yes, this is true...
>
> ...But, here again use of Identity solves a problem:
>
> What if you used FN LN and Street address as primary key?
>
> Then, You have two different AlGores living at the same address!
>
> You would somehow have uniquify these two different records'
> primary keys..
>
> Enter Identity, problem solved...
I think you've got me confused with someone else; I'm saying you SHOULD use
primary keys!
Nevertheless, this still doesn't remove the need to figure out how to
identify entities by natural primary keys; this is part and parcel of
figuring out what defines an entity. For example, if within the context of
my application, a person is defined as something with a first name, a last
name, and a Social Security Number, I sure wouldn't want to have two of
those in my application, whether I used identity columns or not. So, I'd
define a unique index on the table using the natural primary key, in
addition to using an identity column to identify entities within the
database.
As for the "two Al Gores" problem you pose, that indicates that first name,
last name, and street address may be insufficient for use as a natural
primary key. It doesn't mean that there is no valid natural primary key,
just that we need to keep looking. Here's why we need to keep looking: What
if I search for Al Gores, and I find both, and examine all their attributes.
If all the attributes were exactly the same, does this mean that there are
in fact two Al Gores, or does it mean that there's been a data entry error.
Maybe the census taker was seeing double. If the only differentiator is the
identity column, which is metadata rather than real data, then I can't
answer this question. From a good database design perspective, this question
needs to be answered, as I don't want duplicate records. If I have
duplicates, there may be a ripple effect throughout the application - maybe
the people table has a dependent table, let's say
"illegal_donations_solicited", and each of my Al Gore records has one or
more entries in this dependent table. How will I know whether there's one Al
Gore who solicited all the illegal donations, or whether they were in fact
split among multiple Al Gores? Which one would I reward with a vice
presidency, and which one would have to find new interns?
> Now, what do I do with that extra AlGore?
I could suggest what to do with both, but this isn't the correct forum for
that. Not that I'm a Bush fan, either.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
