SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2005
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    your opinions on my database design

    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?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Apr 2005
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    joining to many "lookup" tables is usually not a problem, since they would be accessed via their primary keys, which are indexed by default
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Apr 2005
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot, I'll go ahead with the features tables.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •