Hey there

I'm working on revamping an application I originally helped architect. At the time I first created it, it was my first DB/ASP project and a lot of things in it now seem glaringly wrong about it. Namely, there is one field in the table we use that breaks the cardinal rule of atmocity - it contains several values (maybe as many as a dozen) seperated by commas. We have a userform where the user selects a dropdown and then we try to return all rows where their selected value appears in this list

I have been trying to convince the person overseeing this (my boss, I guess you could say) that we need to create a M:M table and essetially start over. However, he's fixated on the idea that if we create a temp. recordset, it's going to contain more rows than the current table and thus it will be "slower". But aside from being extremely bad database design, I know that parsing out strings is not exactly effiicient.

[quoted text, click to view]

Unless you need to do it to every row in the table in order to search for a
value contained in this field. :-)

[quoted text, click to view]

Using a single value in a field will allow you to index that field, making
searches much more efficient than the table scan required by storing
multiple values in that field. let me illustrate it like this:

Your current table probably contains data like this (using Evertjan's
example):

ID Properties OtherFields
1 1,big,blue ------
2 5,red,medium -----
3 28,red, small -----
etc.

To find the records containing the word "red", the query engine needs to
look at every record in this table. This operation is known as a table scan.
It's an operation which should be avoided. With many rows, I think you can
see that this can be a resource- and time-consuming operation.

For an analogy, think about how hard it would be to find a name in a phone
book if the names were not arranged in alphabetical order.

Now let's look at the case where we break the properties out into a new
table as suggested by Evertjan. We now have a main table:

ID OtherFields
1 ----
2 ----
3 ----

And a properties table (I suspect "Property" might be a reserved keyword, so
you should come up with another name):
ID Property
1 1
1 big
1 blue
2 5
2 red
2 medium
3 28
3 red
3 small

Now we can put an index on the Property column and it will be a snap for the
query engine to find the two records where Property="red". When we use a
join in the query returning these records, you can assure your boss that the
query will not be returning extra records. This sql:

select m.ID, otherfields
from maintable m inner join propertytable p
on m.id = p.id and p.[Property] = "red"

will only return 2 records (try it). And it will do it extremely
efficiently.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

?? wrote on 27 mrt 2004 in
[quoted text, click to view]

using serverside split(",") is not that bad.

[quoted text, click to view]

I would always put a variable number of row properties of a row in your
"main" table in a separate table, one record per property and a pointer
field to the "main table row id". The number of properties of one row
stays unlimited that way.

Searching on such property will be easy, as is listing the properties of
one row, both with a sql join or two seperate sql executions

tblMain:
id name [used to have field: properties ]
1 qwerty [ used to contain: red,curved ]
2 asdfg
3 zxcvb [ used to contain: red,big,$19.95 ]


tblProperties
id point value
1 3 big
2 3 red
3 1 red
4 1 curved
5 3 $19.95


--
Evertjan.
The Netherlands.

[quoted text, click to view]

Yes, it should work, but since it will not be able to use an index, it may
not perform well, depending on the size of the table of course.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

On Sat, 27 Mar 2004 10:43:09 -0500, "Bob Barrows"
[quoted text, click to view]

But a LIKE or CONTAINS query should work, assuming the proper database
and query language, right?

Gotta agree with the rest of the explanations/suggestions though. A
flat-file or comma-delimited format works well for either one-time use
or very small data sets. It's not the best method for general
queries, otherwise no one would have developed any RDBMS to replace
them. :)

Jeff

[quoted text, click to view]