Long SQL Statement Via ASP

[MDW]

Not sure if this is a more Access related question or a
more ASP related question, or both, or neither.

I'm trying to convince another developer to redesign the
schema of a database we use to generate an ASP page.
Here's the Cliff's Notes version of our current setup -

We provide visitors to our Web site a way to search this
database. They are allowed to pick several search
criteria, one of which they select from a long list of
choices. The way that our database is currently set up,
each record has a comma delimited list in the field for
these choices. So if the select box on the form looks
like this -

Apples
Oranges
Pears
Grapes
....

- then one record in this table might have a field named
Fruit that has the value "Apples, Pears, Grapes".

There could be...geez...a dozen or more options for this
value. To accomidate, we build a massive SQL statement
that we pass to the DB. In addition to the SQL to handle
the other search points, the section dealin with this
field contains four or five LIKE operators, to try and
find the value with a comma after it (as if it were at the
beginning of the list), a comma on either side (as if it
were buried in the middle of the list), no commas (as if
it were the only option for this row), etc. etc. etc.

I want to break out that field and make a union table
Records <-> Fruit. However, doing so would entail
modifications to other apps besides this Web site, and my
buddy is leery of doing that unless we're going to
recognize real solid gains from tightening up our design.

So....based on what I've described, do you think that our
app would be better (i.e., run faster, more efficient,
etc.) if we made the change I propose? I estimate that
we'd cut the length of the SQL statement almost in half if
we did this.

[Aaron Bertrand - MVP]

> - then one record in this table might have a field named

> Fruit that has the value "Apples, Pears, Grapes".

I strongly recommend you use a many-to-many relationship, instead of trying
to combine multiple attributes into one element.

> the other search points, the section dealin with this
> field contains four or five LIKE operators, to try and
> find the value with a comma after it (as if it were at the
> beginning of the list), a comma on either side (as if it
> were buried in the middle of the list), no commas (as if
> it were the only option for this row), etc. etc. etc.

Well, if this is your biggest problem at the present time, you could work
around this by thinking outside the box for a moment::

WHERE ',' + column + ',' LIKE '%,apple,%'

However, breaking your fruits out into a fruit table and linking them to the
actual data seems to make more sense. Not only do you avoid parsing strings
to run queries (read: no index!), you can save gobs of spacing by only
actually naming a fruit once, and representing it by a smaller datatype
(e.g. TINYINT = 1 byte) wherever it is needed. This way, too, if Florida
decides to change the name 'orange' to 'gatorfruit' due to a substantial
investment from a certain school, you only have to change the word in one
place in your entire database. Consider:

CREATE TABLE Fruits
(
FruitID TINYINT IDENTITY(1,1)
PRIMARY KEY CLUSTERED,
FruitName VARCHAR(20)
)

CREATE TABLE OtherData
(
SomePrimaryKeyID INT
)

CREATE TABLE FruitLookup
(
SomePrimaryKeyID INT,
FruitID TINYINT
)

[Jeff Clark]

DO IT.
Not only that, you are likely to run into problems in the future that aren't
knowable now.


<anonymous@discussions.microsoft.com> wrote in message
news:02f301c3a4b1$83265540$a501280a@phx.gbl...

>

> >-----Original Message-----

> >> - then one record in this table might have a field named
> >> Fruit that has the value "Apples, Pears, Grapes".

> >
> >I strongly recommend you use a many-to-many relationship,

> instead of trying

> >to combine multiple attributes into one element.
> >

> >> the other search points, the section dealin with this
> >> field contains four or five LIKE operators, to try and
> >> find the value with a comma after it (as if it were at

> the

> >> beginning of the list), a comma on either side (as if it
> >> were buried in the middle of the list), no commas (as if
> >> it were the only option for this row), etc. etc. etc.

> >
> >Well, if this is your biggest problem at the present

> time, you could work

> >around this by thinking outside the box for a moment::
> >
> >WHERE ',' + column + ',' LIKE '%,apple,%'
> >
> >However, breaking your fruits out into a fruit table and

> linking them to the

> >actual data seems to make more sense. Not only do you

> avoid parsing strings

> >to run queries (read: no index!), you can save gobs of

> spacing by only

> >actually naming a fruit once, and representing it by a

> smaller datatype

> >(e.g. TINYINT = 1 byte) wherever it is needed. This way,

> too, if Florida

> >decides to change the name 'orange' to 'gatorfruit' due

> to a substantial

> >investment from a certain school, you only have to change

> the word in one

> >place in your entire database. Consider:
> >
> >CREATE TABLE Fruits
> >(
> > FruitID TINYINT IDENTITY(1,1)
> > PRIMARY KEY CLUSTERED,
> > FruitName VARCHAR(20)
> >)
> >
> >CREATE TABLE OtherData
> >(
> > SomePrimaryKeyID INT
> >)
> >
> >CREATE TABLE FruitLookup
> >(
> > SomePrimaryKeyID INT,
> > FruitID TINYINT
> >)
> >
> >
> >
> >.
> >

>
> I second that motion. Always normalize, unless there is a
> definite performance advantage not to.
> Other selling points: "Maintainability"
> Don't want to be crude, but if it had been built right the
> first time, you wouldn't have this problem.