Coldfusion and SQL with a comma delimeted list in the database

Hi all,

Maybe I'm in the wrong area to make this request but I trust the people in here to know the answer and to reply.

I may have desinged my database incorrectly but as it stands now I have a coaches table. Inside the coaches table I have a column that details the coaches position.

SOme people have more than one position but in order to have only one record per person I use a comma delimited list to state thier positions.

Now I can't figure out what the SQL syntax is to pull a persons name based on only ONE of those choices in the list.

Example: Tfld_coachesInfo_position = Director,Coach

I want to get this person if they are a Director in one curcumstance OR a coach in another.

If this was strictly ColdFusion in a URL I'd simply be able to check something like this by using the CONTAINS keyword.

SQL doesen't seem to have anything like this. I tried IN and LIKE and they either don't return anything OR they return ONLY records that have both.

Hopefully this made sense. Hopefully I'm asking in the right forum.

Nathan

Yes, any time you are storing lists is a single database field, that is almost always an indicator that you need to normalize the database. By far the best short- and long-term solution is to create a positions table, and a positions_users table, and then store what users have which positions in the positions_users table.

However, even though it will be slow it is not very maintainable going forward, I don't see why you can't do something like:

Tfld_coachesInfo_position LIKE '%Director%' or Tfld_coachesInfo_position LIKE '%Coach%'

You should also be able to do with with a string function (in Oracle this is the substr() function, it varies from DB to DB).

most of the time, the comma-delimited lists that i've seen people use are lists of id numbers that implement a one-to-many relationship

the problem with LIKE is that you cannot, for example, just do this --

... where categories like '%3%'

because if the categories value is '1,5,13,17' you will get a false positive

therefore, you end up changing it to

... where categories like '%,3,%'

but this fails to select the row if categories is '3,5,7,15'

eventually you end up doing something like this --

... where concat(',' , categories , ',') like concat(',' , $searchterm , ',')

which works, but will never scale efficiently (always does a table scan)

This is exactly whats happening. Would you agree with Kiteless that my problem is lack of normalization of the database? What criteria can I use to determine (in the future) that I need to normalize? I have this one now about lists in a single field. Any others?

Nathan

another warning sign is a series of columns named foo1, foo2, foo3, ...

see Fundamentals of Relational Database Design for some normalization guidelines (please disregard that the examples are microsoft access, the techniques work everywhere)

Funny, but because I've never actually done this (learned database design very early in my career) I had never even thought of that!

I've set it up where, on a change to the list field, I add a comma to the end and beginning of the list in the table. So the field would adjust on upload from '3,5,7,15' to ',3,5,7,15,' That way, I can just do the straight LIKE '%,3,%'

Not necessarily the best solution, but since, in my applications (and where I can't adjust the tables to a better system), the person adding the information is in a back-end area it's easier on the front end. Although I could be (and often am) wrong.