Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: vernonw@gatewaytech.com
Subject: Re: query speed joining tables
Date: Thu, 16 Jan 2003 15:42:14 -0800
Cc: pgsql-sql@postgresql.org
References: <AC93265Y52EDXCB3VJE2174A9321Z.3e273e9c@kimiko>
In-Reply-To: <AC93265Y52EDXCB3VJE2174A9321Z.3e273e9c@kimiko>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200301161542.14662.josh@agliodbs.com>


Vernon,

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

You're correct.

>But the=20
> 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 th=
e=20
design phase, or when you're using and administering it in production.  My=
=20
general experience is that every extra hour well spent on good DB design=20
saves you 20-40 hours of admin, data rescue, and by-hand correction when th=
e=20
database is in production.

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

VIEWs, TRIGGERs, RULEs and FUNCTIONs.  WIth 7.3.1, SCHEMA as well.  Using o=
nly=20
these structures, I have been able to build entire applications where my PH=
P=20
programmer never needs to know the intracacies of the database.  Instead, h=
e=20
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 o=
nly=20
took me less than a hour to scan over the=20
> 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=
=20
idea to put a delimited list in a field, I'll need to stop recommending tha=
t=20
book.

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco