I'm designing a site that will store information about accommodation, so for each 'property' whether it's a hotel, bed and breadfast or whatever there are a large number of attributes.

I can make a large table that has fields for each, so I might have . . .

free wireless internet
internet access chargable extra
no dogs
dogs by arrangement
air conditioning in rooms
iron in each room
iron available
laundry on site
laundry facilities nearby

etc etc about 30 items in the list

So each one could be a searate field

An alternative would be to pack the data into a small number of single fields then unpack it as necessary, if I pack with appropriate binary then I can use masks to perform really fast searches and matches - but am I throwing away the benefits of using a database?

and . . . which is going to be the most efficient, bearing in mind that the site and database are hosted on a shared server.

I'm concerned that I don't go to a whole load of effort writing the pack/unpack functions when I could have just left mysql to do it.

Opinions welcome <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>

Well from what i understood you could use category and sub categories. So if you have a table call property then in the property table you'll have property_id, property, then another table called property_options you'll have options_id, property_id,options.
Notice that in both table you have the field property_id now for each property you have its own options for e.g.
<pre id=code><font face=courier size=2 id=code>
property table

property_id property
1 hotel1
2 apartments
3 bungalows
</font id=code></pre id=code>
then you links them like this in the property_options
<pre id=code><font face=courier size=2 id=code>
property_options

options_id property_id options
1 1 Laundry Facility
2 1 Microwave
3 2 Iron
4 2 Laundry on Site
5 1 Dog Arrangement
6 2 Air cons
7 1 Air cons
8 3 No Dogs
9 3 Dogs Allowed
</font id=code></pre id=code>
You could have a huge lists in the property_options table. Then retrieve each just by using the field property_id in both tables.

Roddy

Thank you for your suggestions, I like that . . . it would be VERY flexible and allow for a huge number of options for each property. On balance I shall restrict the number of options to a predetermined list and only add to that list if there is a real need.

I set out to implement this and for a couple of the fields I used a separate table for the property attributes, and for some attributes I used enum field types.

For the separate tables it was easy to put in a dynamic select box, and for the enums I used radio buttons. Where the difference really showed up was when I found I needed some more options, for the separate tables it was just a matter of adding a new row to the table and the job was done, not so for the enums.

I am now in the process of converting most of the fields to separate tables, it will make the maintenance so much easier.