Advice on dealing with large amount of checkbox data

[chuckylefrek]

I am creating an accommodation site and need to include quite a lot of information about each accommodation listing especially self-catering.

Currently I have a long form with check boxes for the following:

Electricity included
Electricity by coin operated meter
Electricity charged separately,
Heating included
Heating charged separately
Bed linen provided
Bed linen available at extra cost
Towels provided
Off road parking
TV
Video
Microwave
Iron
Drying facilities
Tumble dryer
Cot
Children welcome
On-site shop
On-site food take-away
on-site dog
Children’s play area
Indoor swimming pool,
Outdoor swimming pool
and there are more........

My current thinking is I will need a separate field for each of these items in my database - well this is the only way I know how to store the information.

However I am wondering if there is a better way to store the information?

[viveknarula]

Well, you can store all these values with an delimiter like comma (,) and store them in one field in the database.

This way you can add new checkboxes later on without needing to create an new field in the database. However you need to pre-determine the sequence of the checkboxes so that you can use correct value for correct checkbox while retrieving and showing the values.

[chuckylefrek]

Thanks for the reply - can you elaborate on this please?

If I stored them all in one field, would the value look like an array and would I just use boolean values to indicate whether a feature was included or not e.g [1,0,1,1,1,0,0,0,]

Or would it be best to store a meaningful string for each and to only include details of those facilities that are chosen?

[viveknarula]

Thanks for the reply - can you elaborate on this please?

If I stored them all in one field, would the value look like an array and would I just use boolean values to indicate whether a feature was included or not e.g [1,0,1,1,1,0,0,0,]

Or would it be best to store a meaningful string for each and to only include details of those facilities that are chosen?

Well it depend upon you how would you like to store. However in my opion you should go for 1 and 0, if an checkbox is checked then 1 and if not then 0.

Of course, the value that will save in the database will be as 1,0,1,1,1,0,0,0,... you need to explode this value by delimiter (,) while showing the values. and you know the sequence of the values corresponding to the checkboxes. with exploded values in array format you can easily show the checkboxes.

[chuckylefrek]

Thanks again.

I am a little concerned that this method of storing the data is very reliant on the sequence of values. If at a later date one of the checkbox options was removed, this could mess up the sequence. Is there a way to store all the data in one field but have it more meaningful?

[viveknarula]

I am more concern about the relation of one value to an checkbox. Say, the first checkbox's value should relate to the first value of our comma separated string. If an checkbox has been removed then our values will come in that sequence only ( will not have the value for the removed checkbox)

[AnthonySterling]

Morning,

If you don't plan on doing any intense reporting on these fields, you could store them as a serialized array within the database.

This would ensure you could keep the key / value pairs and allow PHP to use the data natively.

[chuckylefrek]

Morning Anthony

I certainly hadn't planned any intense reporting so it sounds like I should make a coffee and read up about serialized arrays - funny I am sure I used one of those the other day for something else

Is this a serialized array?

$accommodationTypeFriendlyNames = array('hotel'=>'Hotel', 
						'guestHouse'=>'Guest House',
						'bedandbreakfast'=>'Bed and Breakfast',
                                                'other'=>'Unclassified Accommodation Type');

[AnthonySterling]

Nope, that's an associative array.

Here you go.

Array
(
    [0] => Array
        (
            [Bathroom] => 1
            [TV] => 0
            [Dog] => 1
        )

    [1] => Array
        (
            [Bathroom] => 0
            [TV] => 1
            [Dog] => 1
        )

    [2] => Array
        (
            [Bathroom] => 
            [TV] => 0
            [Dog] => German Shepard
        )

)
a:3:{i:0;a:3:{s:8:"Bathroom";i:1;s:2:"TV";i:0;s:3:"Dog";i:1;}i:1;a:3:{s:8:"Bathroom";i:0;s:2:"TV";i:1;s:3:"Dog";i:1;}i:2;a:3:{s:8:"Bathroom";N;s:2:"TV";i:0;s:3:"Dog";s:14:"German Shepard";}} 

Serializing the array converts it into a easily transportable string, which when needed, you can unserialize back into an array.

[chuckylefrek]

Thanks Anthony

Couple of questions:

Just out of interest, what would the implications have been if I had used a separate field for each feature in the database and used TINYINT(1)? Would this seriously affect the perfornance of the database if I had say 5000 accommodation listings?

I just thought of an alternative in that I could have a separate features table and a features to accommodation linking table? How would this solution compare?

You previously asked if I would have any intensive reporting. If I wanted to have an advanced search function to allow people to search for accommodation that included one of these features, would this seriously affect my options in terms of serializing the data?

[AnthonySterling]

If I wanted to have an advanced search function to allow people to search for accommodation that included one of these features, would this seriously affect my options in terms of serializing the data?

If this is the case, you should probably add these columns to the table containing the properties, I don't think there is a need for another table.

Performance should not be limited by it, I think.

[Hammer65]

I would strongly recommend against consolidating this data into a single field. The only reason to store this information, is to make it available and useful to users of the application. Part of making information useful, is allowing the abaility to sort, search and sift through information to get to exactly what you are looking for. By consolidating it in a single field, you are making that task impossible, if not impractical.

Having a table with just the basic information on an item, and then having a "features" table for more detail is a tried and true solution.

[chuckylefrek]

Thanks again Anthony for your reply.

The reason I thought of adding them to a separate table is because each accommodation type has different features. For example a self catering apartment may have a long list of features that are not applicable to a hotel. So say in total there were 100 different features spread across the different accommodation types.

Only 6 of these 100 features may be applicable to a hotel however I guess if they are all stored as separate fields in the accommodation table then each hotel listing must store 100 fields - does that make sense?

Anyway I don't want to get too fussy about this as I am starting to get the feeling this website is turning into a mammoth task - been working on it for over 6 months. Whilst I think I am 90% complete, I have a feeling the last 10% is going to prove very time consuming

[Vali]

Well, you can store all these values with an delimiter like comma (,) and store them in one field in the database.

This way you can add new checkboxes later on without needing to create an new field in the database. However you need to pre-determine the sequence of the checkboxes so that you can use correct value for correct checkbox while retrieving and showing the values.

If you do that, or store them in bitfields, you won't be able to do selects on them (ex: how many users had X selected)

You should store them using 3 tables:
Table 1: data (id/label = list your provided)
Table 2: user data (user_id, etc)
Table 3: link table (link_id (optional), data_id, user_id)

That way, you can run reports on anything you want.
Also, if you want to add an option, all you do is insert a record in Table 1, and your done (your php should select / loop those records to display them).

To read them in your code, name all the checkboxes the same, with their values to their corresponding IDs.
When the user posts the form, an array of all the selected checkboxes will be posted. So, to save them:
#1 loop posted data and validate it
#2 delete from Table 3 where user_id = X
#3 loop posted data and insert in Table 3 whatever was posted for that field name.

(Note: this pseudo code only works assuming your displaying all the options from the DB.)

[crmalibu]

chuckylefrek, use a seperate table like Vali described. Btw, this really is a question for the database forums, not php, as it's a database design question.

Also, before you do anything else, spend some time reading about database normalization. It's a very important concept when designing databases.

[Cups]

Yeah, you might have a table called "features".

features
======
1 - indoor pool
2 - outdoor pool
3 - tv
4 - video
5 - walkman
6 - on site dog

then you'd just have a lookup table for each organisation/feature.

place_feature
==========
1 - 1
1 - 6

As explained it will be easier to allow ppl to filter by a feature, "show me all places in county x which have any kind of pool"

When it comes to hotels which are simpler and dont need to go through 100 items, you could create families of features.

org_type_family
===========
1 - Hotel
2 - B+B
3 - Camping
4 - Guest house

Then do similar on their admin page, eg hotels:

family_feature
===========
1 - 1 (indoor pool)
1 - 2 (outdoor pool)
1 - 3 (tv)
3 - 6 (they don't generally have an onsite dog)

So that hotel staff only see items 1,2,3 - then if they want to see "the whole lot", then they can.

[chuckylefrek]

Thanks guys for all the help and sorry that this is really a database question.

I think I am getting my head around this.

So I will create a table called features as follows:

id
feature

CREATE TABLE `features` (
  `id` smallint(6) NOT NULL,
  `feature` varchar(40) NOT NULL,
  PRIMARY KEY  (`id`)
)

I will create an admin page to enable me to add features as follows:

$feature = $_POST["feature"];

$query = "INSERT INTO features (feature)
				VALUES ('$feature')";

I will also create an feature_accommodation table to link features to accommodation lisings as follows:

featureId
accommodationId

CREATE TABLE `feature_accommodation` (
  `featureId` smallint(6) NOT NULL,
  `accommodationId` smallint(6) NOT NULL
)

I am not quite sure how I populate this table. When a user creates a new accommodation listing, do I literally display checkboxes for all the features in the features table then when the form is submitted, insert a row into the feature_accommodation table for each feature the user has checked?

I like the idea of the families of features - can you explain how this would work please. Would I need to add another table linking each feature to a family as follows

featureId
familyId

Then have an admin page to add features to the various families?

Does the above sound about right?

Seems like lots of tables but perhaps this is what database normalization is all about?

[Cups]

I am not quite sure how I populate this table. When a user creates a new accommodation listing, do I literally display checkboxes for all the features in the features table then when the form is submitted, insert a row into the feature_accommodation table for each feature the user has checked?

$accom = 233

"insert into feature_accommodation values( 233, 1 ), (233, 2), (233, 3")

later ....

"select accom_id from feature_acommodation where feature_id in (1,2)"

Show all accomodation place with some kind of pool.

Then have an admin page to add features to the various families?

Yes, if you want to. Else just maintain an array if you prefer.

$features['hotel'][1];
$features['hotel'][2];
$features['hotel'][3];
$features['hotel'][17]; 

You dont have to make an admin screen for everything, that could be picked up from an ini file that you override manually. Depends who the admin screens are for, you? someone you sell your system to? A hotel?

Does the above sound about right?

yes, yes sounds completely normal (eek a pun!)

Seems like lots of tables but perhaps this is what database normalization is all about?

Yes, welcome to the club. Its not hard, just harder to draw on a single piece of paper, you might buy some coloured crayons too.

It's part of the learning curve, you may even start off making too many tables and find ways of reducing them - that seems to be part of the curve too ...

The trick, as ever it seems, is to try and get as good a handle on how you want to extract information for users of the site, what does the best other site in that niche do? Find out from your client which reports they actually need most in order to maximise the profit they derive from their business using your tool. Then you are in a position to start drawing how your tables will link up together.

Its not about websites, its about making money for people.

This long term project of yours might feel like a bit of a drag now, but lessons you learn should also serve to bolster your belief that you can always add more and more value simply by being confident you can use further normalization, or add more data.

[Vali]

Make sure to set some index/key on your feature_accommodation table, if you want to do selects on it.

[TomB]

I'd do it with bit flags, it allows advanced reporting and simplifies database structure.

define('ELECTRICITY_INCLUDED', 0x1);
define('ELECTRICITY_COIN', 0x2);
define('ELECTRICITY_CHARGED', 0x4); 

Give each checkbox the same name e.g.

<input type="checkbox" value="<? echo ELECTRICITY_INCLUDED; ?>" name="features[]" />
<input type="checkbox" value="<? echo ELECTRICITY_COIN; ?>" name="features[]" />
<input type="checkbox" value="<? echo 'ELECTRICITY_CHARGED; ?>" name="features[]" />

then, to store the data:

$features = 0;
if (is_array($_POST['features')) {
    foreach ($_POST['features'] as $feature) {
       $features |= $feature;
    }
} 

Now store $features (if they're all checked it will be 7 in this example).

And then for querying based on features you can do this:

$mysqli->query('SELECT PlaceName FROM places WHERE ' . ELECTRICITY_INCLUDED  . ' & features'); 

Which will bring back any place with electricity included as a feature.

It also allows for easy negative reporting:

$mysqli->query('SELECT PlaceName FROM places WHERE ' . ELECTRICITY_INCLUDED  . ' & ~features'); 

(All places which don't have electricity included)

Which isn't easily possible with the table method (you'd either need a sub query or an extra on/off field in the features table).

[crmalibu]

Wouldn't using bit flags limit it to 31 possible values on the php side, and 63 on the mysql side? I guess you could use GMP for the php side to match the 63 on the mysql side.

But this still seems to be a pretty severe limitation for this system.

[Cups]

I completely overlooked bitmasks and I like the idea of them very much, probably not on this occasion as pointed out. If anyone else is interested here's a recent, and very clearly written article Denormalisation with bitmasks.

[AnthonySterling]

Off Topic:

Thanks Cups

[chuckylefrek]

Hey thanks peeps - just coming back to this now but looks like there is a lot to digest. I will have a read and hopefully not have to ask too many more questions

[chuckylefrek]

Quick question

If I know the accommodationId and I want to display all the features for this accommodationId, I basically need to select all the records in the feature_accommodation table using something like the following:

$accommodationIdValue = 22;

$result = "select * from feature_accommodation where accommodationId = '$accommodationIdValue' ";

This would return a record set of all the featureId values associated with the particular accommodation listing. However I need to get the actual feature title from the features table.

Would I need to loop through this record set and call a query for each featureId?

$featureId = ?
$result2 = mysql_query("select * from features where featureId = '$featureId'");
$row = mysql_fetch_object($result2);
$featureTitle = $row->feature;

Or do I need to use some clever JOIN type code to get all the necessary data back in one step?

I will have a stab at what is necessary - this may be totally wrong

$accommodationIdValue = 22;

$result = mysql_query("select featureId, feature FROM feature_accommodation INNER JOIN features ON accommodationId = '$accommodationIdValue'");