SitePoint Sponsor |
|
User Tag List
Results 1 to 5 of 5
-
Jan 26, 2006, 03:52 #1
- 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?
-
Jan 27, 2006, 10:33 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- 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.
-
Jan 28, 2006, 03:56 #3
- 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
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.
-
Jan 28, 2006, 05:35 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- 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
-
Jan 28, 2006, 07:13 #5
- 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