From: Josh Berkus <josh@agliodbs.com>
To: vernonw@gatewaytech.com
Cc: pgsql-sql@postgresql.org
Subject: Re: query speed joining tables
Date: Thu, 16 Jan 2003 15:42:14 -0800
Message-ID: <200301161542.14662.josh@agliodbs.com> (raw)
In-Reply-To: <AC93265Y52EDXCB3VJE2174A9321Z.3e273e9c@kimiko>
References: <AC93265Y52EDXCB3VJE2174A9321Z.3e273e9c@kimiko>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, vernonw@gatewaytech.com
Subject: Re: query speed joining tables
In-Reply-To: <200301161542.14662.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox