[thelist] DB design question

[Joel Canfield]

--
[ Picked text/plain from multipart/alternative ]
> Being as how database design is not my strong suit (or
> even my weak suit) I thought I would look for a little
> direction on this one.  I'm trying to decide how to set up
> some tables that will describe a property listing.  The
> problem I'm having is deciding what to do with descriptions
> of amenities.  A listing will obviously have an address and
> price in one table but what about boolean-type fields such as
> fireplace-yes/no, carpeting-yes/no, etc. of which there may
> be 20 or 30.  I want to be able to search on these so how
> should they be linked back to the main table?  Any ideas
> mucho appreciated. DS

First create these tables:

Property table:
address
price
square footage
etc.

Amenities table:
amenityID
amenityDescription

PropertyAmenities table:
Property.address
Amenities.amenityID

You're creating a 'many to many' connection between Properties and
Amenities; that is, a Property can have many Amenities, and an Amenity can
exist in multiple Properties.

That's tough to sort out unless we create the 'lookup' table we called
'PropertyAmenities' which connects one amenity to one property in each
entry.

Now, you put the amenities in their table, and reuse at will; put the
properties in their table, and pile on as many amenities as you want. But
you list each property's amenitites in the PropertyAmenities table, not the
others.

<shameless plug>
You might give my article at
http://evolt.org/article/Beginning_Database_Design_Part_I/18/27137/index.htm
l  a read; goes into a bit more detail. I'm also available for further
questions.
</sp>

joel