[thelist] DB design question
rudy
r937 at interlog.com
Fri Dec 6 16:37:01 CST 2002
> Yep, I just thought that finding "1313 Mockingbird Lane" would be
> slower than searching for '798', but maybe the indexing takes care
> of that for us.
yes, it's slower, and yes, indexing takes care of it, but that's not, in
itself, a reason to create a surrogate key
think about it for a second -- under what conditions would you find yourself
searching for a surrogate key value like 798? how do you know what that
value is, and which property it is?
thanks for the kudos, joel, but in this case the address key field seems too
big to me
the real deal-maker, heart-breaker (obscure pat benetar reference), in using
a natural primary key, is whether there are foreign keys pointing to it,
like here --
> PropertyAmenities table:
> Property.address
> Amenities.amenityID
for a given property, the address foreign key in this table will be repeated
how many times? a property has on average how many amenities, a dozen or
more? this table will balloon quickly
> Another more important issue might be this - same street address,
> different town. It's the full address that makes it unique. There are a
> lot of Elm Streets out there...
good point
while we are on the subject of primary keys, remember that the purpose of a
primary key is to ensure that each row is unique
the database goes to great lengths to guarantee that primary key integrity
is never compromised
so let me pose a question --
if you create a surrogate primary key, like an autonumber (which i think
makes the most sense in this example of property addresses), then what's the
"real" key? the address, right? including apartment number, and probably
city, too, right?
but how is the database supposed to know that the address is the "real"
primary key, if you use an autonumber as the primary key?
and so the question is, what's to stop the table from having two rows with
the exact same address?
;o)
rudy
More information about the thelist
mailing list