Which is the feasible solution to store Multiple Id's from checkboxes in PHP?

i have a database table prop_amenities which have following columns

here is the开发者_JAVA百科 create table i am using for the above table

CREATE TABLE `prop_amenities` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `amenity_id` int(11) NOT NULL,
  `property_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `propAmenities` (`amenity_id`,`property_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

i will be storing multiple amenity id's for a single property_id, here is the example of the values i have stored.

however this works just fine while inserting, but while updating it is a real problem, if for example i have to delete and add more amenity_id i have to perform two database trip first delete the value and then add the new one.

to make it feel nicer i thought of using serialize() or json_encode() to store the array of amenity_id in database. this way i could reduce the number of column i.e it will be one column per property_id.

Database column after using serialize()

What do you think which solution better fits the situation here?

Thank you

You need to ask yourself if you will ever need to care in a query which ones are selected. If the answer is an absolute "no" then serialization is acceptable. Otherwise, stick to the properly-normalized form.

If you do not have more than 64 possible amenities, the SET datatype would work nicely to contain all the amenities for a particular property in a single column, while being much easier to query than a serialized value would be.