[thelist] DB design question

[rudy]

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

that's a really good question, it shows a good understanding of the
structure

see the following articles for more on this --

   Exactly one row? At least one row? (Part 1)
http://searchdatabase.com/ateQuestionNResponse/0,289625,sid13_cid497037_tax2
85649,00.html

   Exactly one row? At least one row? (Part 2)
http://searchdatabase.com/ateQuestionNResponse/0,289625,sid13_cid497038_tax2
85649,00.html

because of how convoluted the queries are, and how frequently the users
of the property database are likely to search on combinations of amenities,
it is quite likely that a design decision will be made to denormalize
("flatten") the amenities into the property table

whether this denormalization is a good idea or not, i shall leave to the
more theoretical of the theoreticians in the crowd...

... but if any of them speak up to complain about the denormalization, i
shall ask them point blank whether they have ever implemented a person table
with first name and last name columns side by side, because that's exactly
the same kettle of fish

yes, amenities may be added in future, and yes, this may disrupt the table
design, unless foresight is employed and a few additional columns like
Other1 and Other2 are added

best design ever?  maybe, maybe not

pragmatic?  you betcha


rudy