[thelist] DB design question

[Ed McCarroll]

> Every time you added a new amenity, you'd have to alter the table structure.
> If the amenities are in a separate table, adding a new one is simply another
> record in the table, a much less complex proposition. (See my previous
> response for more details.)

I'm probably missing something here, but...

The thing I don't see how to address in a many-to-many approach is
how you would search for multiple criteria.  What kind of JOIN are
you going to use that will allow you to SELECT rows that match values
in two different amenities, e.g. bedrooms=3 and baths=2?  If you can
do that efficiently, could you please educate me; I sure could use it.

I've seen output from the Southern Calif regional real estate multiple
listings service, and it appears that they have set up a table with
a column for every amenity they could imagine, and then a few more
named "Other1", "Other2", ...  The "OtherX" columns accommodate
descriptions so that even they can be searched for keywords ("heliport").

> You ... don't
> need to add an artificially unique identifier when you already have one -
> address. You're almost certainly not going to have two properties at the
> same address, so use that field instead of creating an artificial one.

I once took a job in a personnel office, and at first I couldn't understand
why they didn't use Social Security numbers as the primary key for employees.
Shortly thereafter, someone came in with the paperwork requiring me to change
their Social Security number.

You are quite right, you are "almost" certainly not going to have
two properties with the same address.  But if you are given an
erroneous address, or somebody commits a data entry error, you
might have a duplicate key, and you will have a can of worms with
regard to referential integrity.  If, after you've used a street
address to link to a number of amenities, and then you discover
that the address must be changed, you also have to clean up all
the links.  (Although, if you're using MS Access, you can set it
up to do that automatically.)

The thing I like about using an auto-number as a primary key is
that there's no way that the real world can come up with an
exception to the rule; Murphy's law has a much smaller domain
within which to operate.

Anyhow, that's my two cents worth.  I look forward to learning more.

Regards,

  Ed

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Ed McCarroll                          MailTo:Ed at ComSimplicity.com
ComSimplicity                                      (310) 838-4330
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -


> -----Original Message-----
> From: thelist-admin at lists.evolt.org
> [mailto:thelist-admin at lists.evolt.org]On Behalf Of Joel Canfield
> Sent: December 06, 2002 1:02 PM
> To: 'thelist at lists.evolt.org'
> Subject: RE: [thelist] DB design question
>
>
> --
> [ Picked text/plain from multipart/alternative ]
> > Why do you need to put amenities in a separate table.
> > Couldn't you have a column for each type of amenity in the main table?
>
> Every time you added a new amenity, you'd have to alter the table structure.
> If the amenities are in a separate table, adding a new one is simply another
> record in the table, a much less complex proposition. (See my previous
> response for more details.)
>
> joel
> --
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
>
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt !
>