Re: query speed joining tables

Vernon,

> The other way to build a query string is used on selection operation for
multiple table joined and/or involved. A query
> string is built dynmically due to whether or not any fields are examined.
The characteristic of the application is that
> among of many fields a user may only want search on a few selected fields. I
think this approach is better than to have
> all fields listed and fill in "%" for fields the user doesn't want to search
on. (Please correct me if I'm wrong).

You're correct.

>But the
> building query function is as long as more than one hundred lines.

Sure. It's a question of whether you want to spend your DBA time during the
design phase, or when you're using and administering it in production. My
general experience is that every extra hour well spent on good DB design
saves you 20-40 hours of admin, data rescue, and by-hand correction when the
database is in production.

> Please elaborate the above statement. I don't know any 'toolkit to let us
"abstract" the normalized design into
> something the users can handle', other than something like View.

VIEWs, TRIGGERs, RULEs and FUNCTIONs. WIth 7.3.1, SCHEMA as well. Using only
these structures, I have been able to build entire applications where my PHP
programmer never needs to know the intracacies of the database. Instead, he
is given an API for views and data manipulation functions.

> I waited for the book from the local library for more than a month, but only
took me less than a hour to scan over the
> whole book and grip the multivalued table design idea.

Hmmm. I'll need to look at it again. If he's suggesting that it's a good
idea to put a delimited list in a field, I'll need to stop recommending that
book.

--
-Josh Berkus
Aglio Database Solutions
San Francisco