[thelist] DB design question

Joel Canfield joel at spinhead.com
Fri Dec 6 17:48:01 CST 2002


--
[ Picked text/plain from multipart/alternative ]
> thanks for the kudos, joel, but in this case the address key
> field seems too big to me

Fair enough

> 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

Ah; good point.

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

So, we're facing increased complexity in assuring that, while each record is
unique, the *real* key is also still unique.

Isn't it gonna get pretty complex to verify uniqueness on address,
apartment, and city for each entry? Or, once we build the constraints into
the db, is that okay?

joel



More information about the thelist mailing list