Efficient MySQL structure for linking features to accommodation listing

Question

I'm building an accommodation rental site for a specific town. It will include, Houses, Resorts, Hotels etc.

I'm looking for advice on how best to link Property Features (Air-Con, Swimming Pool etc.) to individual properties.

I have a table of around 50 Property Features set up as feature_id, feature_category, feature_name.

What would be the best way to store which features relate to which property?

Would a column in the property table (prop_features) containing an array of feature_id be the best way?

The only example I've managed to find and be able to dissect the DB showed the features added as feature_1, feature_2 etc. which seemed really inefficient as some properties may only have feature_1 and feature_49 for example. Each one was added as a column to the property_table.

I'm new to creating databases from scratch, so I'd be very grateful for any advice on how best to start with this section of my project. (It's also why I'm not having much luck Googling it, as I'm not sure how to put it in more general terms that might yield me a solution).

I recommend including the structure of your property and feature tables as well as a few sample entries from each for more specific answers. — Jonathon Ogden, Jul 29 '16 at 10:08

score 1 · Accepted Answer · edited May 23 '17 at 12:08

One solution would be to have an intermediate table that joins properties to features like so:

CREATE TABLE propertyfeatures (property_id INT, feature_id INT);

If we have a property called Acme Hotel (property id 1) that has air conditioning (feature id 2) and swimming pool (feature id 4), the data would look something like:

property_id | feature_id
1             2
1             4

To retrieve features per property (excluding properties without features) a simple query would be:

SELECT 
    p.property_name,
    f.feature_name,
    f.feature_category
FROM property AS p 
INNER JOIN propertyfeatures AS pf 
    ON p.property_id = pf.property_id 
INNER JOIN features AS f 
    ON pf.feature_id = f.feature_id
GROUP BY p.property_id

Note: I have made assumptions about table and column names in your existing database. You'd have to adjust the above accordingly.

The only example I've managed to find and be able to dissect the DB showed the features added as feature_1, feature_2 etc. which seemed really inefficient as some properties may only have feature_1 and feature_49 for example. Each one was added as a column to the property_table.

Although this can be done, you're correct in that it's inefficient, or rather, it's awkward to maintain. It's referred to as pivoting because you're changing unique row values into multiple columns. For example, what if a new feature (e.g. Free Wifi) was added? It's not a case of simply inserting a new row of data as it would be with the intermediate table, you'd have to create a new column to support that.

Not only that, but you would still have to define the feature columns manually or dynamically. For reference, take a look at MySQL Pivot Table which demonstrates both manual and dynamic methods.

Thank you, not even considered this as an option - as I said I'm very new to this as far as structuring one from scratch. — webecho, Jul 29 '16 at 10:07
@webecho You're most welcome. I am making edits to include responses to some of your other questions. — Jonathon Ogden, Jul 29 '16 at 10:07
thanks - great to see an example using the same names as what I'm trying to do, makes it a lot simpler to get the concept. Will give this a go tomorrow morning. One question, when you've declared propertyfeatures AS pf ... is this this simply to make the following part of the query quicker to type? i.e ON property_name.property_id = property_feature.property_id is the same as p.property_id = pf.property_id. (sorry to ask, but I quite literally started with MySQL (from scratch) about 10 days ago) — webecho, Jul 29 '16 at 10:57
Quicker to type is one benefit, yes. `AS` allows you to assign an alias (i.e. friendly name) to something for easier reference elsewhere in your query. You can define an alias without the `AS` like so `features f` but my personal preference is to use `AS` since it's explicit and easier to identify aliases when reading through much larger queries. — Jonathon Ogden, Jul 29 '16 at 11:02
@webecho don't forget to accept this answer if it helped you :) — Philipp, Jul 29 '16 at 16:04

score 1 · Answer 2 · answered Jul 29 '16 at 09:57

One simple way would be to add another table to your database having the columns. The keyword to this approach is "junction table", it is pretty basic in database design.

property_identifier | feature_identifier (feature_id in your case)

In this table you can display the connection between the properties and specific features.

So you could say property with property_id 1 has a pool (feature_id: 2) and a nice kitchen (feature_id: 23)

So the table would look like this:

propery_id | feature_id
  1        | 2
  1        | 23

Thanks Philipp "Junction Table" has given me a heap of stuff to start reading on google. much appreciated — webecho, Jul 29 '16 at 10:10

Efficient MySQL structure for linking features to accommodation listing

2 Answers2