While I agree that it's evil, I didn't have anything to do with it. I just have to deal with it now : )

However, I don't see how your suggestion could deliver the same functions. Basically, the columns in Table1 are named for actions one could perform on an appointment and the comma seperated lists are the products one would perform those actions for on a given appointment which is detailed by that row. Though the number of actions is fixed (relatively), the products vary with every appointment. so how does your structure above provide the ability to have varying products associated with any number of actions and also correlated to one another as a coherent appointment? I've been staring at your tables and can't quite get it...

-- I'm a solipsist, and so is everyone else. (think about it)

Simple - table 1 contains your actions:

id   action
---------------
 1     ls
 2     cd
 3     file
 4     zonk
[download]

Table 2 contains your list of products:

id   product
-----------------
 1     /usr/bin
 2     /home
 3     /usr/tmp
[download]

And finally Table 3 is a cross reference of the two, called product_actions:

p_id   a_id
-------------
 2      3
 1      3
 2      4
[download]

so something like:

SELECT actions.action, products.product
FROM   actions, products, product_actions
WHERE  actions.id = product_actions.a_id
AND    product.id = product_actions.p_id
[download]

Would produce:

file /home
file /usr/bin
zonk /home  #don't try this one at home kids!
[download]

At least that's what I think that extremely had in mind. Your results will vary, and from what I hear you saying, it sounds to me like you are working with a very un- normalized database.

Hope this helps,
Jeff

I see what you're saying now.

1. Yes, it's de-normalized, but I don't think speed was the reason, if you catch my drift : )

2. I like this idea and will see if I can coerce the DBA to try it...I just need to figure out how to add the third layer here : ) This also needs to have "appointment ids" in table 3, and the select would need to grab everything for a particular "appointment id" and then sort id for output like:

Appointment:
product: action, action, action
product: action
product: action etc. etc.
[download]

3. The biggest trouble I could see with this would be I would need to do many many inserts for appointments with a lot of products involved in order to populate the records properly. Not too big a deal, but more than I had planned. Off to test : )

-- I'm a solipsist, and so is everyone else. (think about it)