[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?



More information about the thelist mailing list