Help exploding an integer string and converting it to an array

or should I further normalize the db so I don't have to?

[travelbuff]

Hello:

I am working on a travel specials management system. The 'specials' table in the db has a resort_id fk to the 'resorts' table to identify what resort the special applies to. The resort entry in the 'resorts' table has fields for room categories - room_cat_1, room_cat_2, etc. I need to have a field in the 'specials' table that identifies what room categories that the special applies to at the particular resort.

One idea I had was to have a field in the 'specials' table that just contained integers for the room categories, so room_cat_1 would be entered into the specials table as 1, so if the special applied to room_cat_1, room_cat_3, and room_cat_5 the entry in the room_cats field in the 'specials table' would be '135'.

So my question is, when I retrieve the data, what kind of syntax would I use to explode the integer string and convert it to the field correct field names in the 'resorts' table?

Is my approach reasonable? Is what I really need another table called 'room_cats' that contains a resort_id fk and a row for each room cat? I know that is what the rules of normalization call for, but there will be about 40 resorts and a max of 8 room cats for each resort so it seems that fully normalizing would actually create more records....

Thanks,

Mark

[coopster]

I would normalize now if I were you. 40 * 8 = 320 rows which is a very small table. Also, consider this -- you get a resort that is doing quite well and decides to expand the business. A decision is make to increase the room categories to 16 or whatever. Now your array that is holding the (135) might actually mean category 13 and category 5 rather than the 1, 3, 5 you originally intended. See where this is going? Normalize now is my opinion.

[coopster]

Another consideration -- there comes a need to create a search page that allows people to find specials for a certain category. It would be much easier to search using table JOINS rather than parse a particular field designed as an array of values.

Normalization just makes things so much easier down the road and it is so easy to structure now as opposed to redesigning later.

[travelbuff]

Coopster, the more I think about it the more I see that you are correct. Thanks for the good advice.