your opinions on my database design

[annette66]

I'm building a database for a holiday rentals site and would really appreciate comments on how best to approach it's design.

At the moment I have tables set up with summary details for each property in order to deal with property searchs like this:

1. prop (summary property details for all properties - the following is just an extract of the columns):

prop_id (PK)
address NOT NULL
bed_no NOT NULL (indexed)
bed_nomax NULL

The bed_nomax column records the total number of bedrooms for properties available as separate units (e.g a property split into a 1 bed, 2 bed and 3 bed apartment and you could rent out one or all or any combination - bed_nomax would be 6). This is so a search for a rental property with 6 bedrooms would pick up that property.

2. specialist_id(the different types of holidays that can optionally be searched on eg skiing, golf etc not applicable for a lot of properties):

activity_id (PK)
activity

3. specialist_prop (properties with specific activities - can have more than one activity)

activity_id (indexed)
prop_id (indexed)

4. restrict_id (restrictions like no smoking etc)

restict_id (PK)
restrict

5. restrict_prop (properties with restrictions - can have more than one restriction)

restrict_id (indexed)
prop_id (indexed)

6. multi_units (the following is just an extract of columns and lists all the individual units for multi_unit properties)

prop_id (indexed)
bed_m (the bed no for each unit)

I'm now need to create tables to record the detailed information for each property that will be displayed when a property is selected from the search. There would be about 50+ bits of information with things like microwaves, cd players, linen etc along with the blurb about each property.

I'm wandering if I should normalise this and have tables for feature types and feature type by prop_id with the advantage that it would be scalable or just stick to a single table with multiple columns. As it stands if I create a single table for these details, I would be using JOINS to the summary tables to display info for a single property.

I know this question gets asked a lot but the thing is at the moment there are only 150 properties and the projection for 600 max over the next couple of years. With such a small number of rows is it worth splitting the information up into seperate tables?

[r937]

interesting question

your tables are fine, by the way, except i couldn't understand what table 6 is for

if it were me doing it, i'd probably not define a lot of individual columns, i'd use the features type table, which you acknowledged was scalable -- it's also easier to work with

i didn't understand this:

As it stands if I create a single table for these details, I would be using JOINS to the summary tables to display info for a single property.

what summary tables?

[annette66]

Thanks for responding to me again.

your tables are fine, by the way, except i couldn't understand what table 6 is for

With multi-unit properties the details in the prop table are totals for all the units i.e if they have a house split into a 1 bed and 3 bed it would be recorded as a 4 bed and the seperate details shown in the multi-units table. If I didnt create table 6 there would be more than one row of info in the prop table. I didnt want this as I wanted to allocate one unique auto-incremented prop_id per property so that when a new property is added (by the owners via cms) an auto-incremented prop_id is recorded in a members table and they would only then be able to update info for their specific property. It also means that the other tables joined can be joined on the prop_id and I can display the results of a search with a single entry per property. I might be wrong but I just thought it would be easier to do it this way.

As it stands if I create a single table for these details, I would be using JOINS to the summary tables to display info for a single property.

Sorry for not being clear - I tend to think of the tables I built to handle the searches (i.e the first 6) as summary tables and so just referred to them as that without realising it would mean nothing to anyone but me. I suppose it might be a bit of a pointless remark, but I was trying to say that as I'm already going to be joining in up to 6 other tables should I JOIN another 2. Actually now I have 2 further tables to JOIN: one for prop type - e.g house, apartment, chalet, condo etc and another for images. So I'd be joining 10 tables - is this too many? i.e would there be an effect on perfomance.

[r937]

joining to many "lookup" tables is usually not a problem, since they would be accessed via their primary keys, which are indexed by default

[annette66]

Thanks a lot, I'll go ahead with the features tables.