Hi,
I am using MySQL 4.0.*
Have two distinct problems regarding database design. (didn't want to spam with several topics).
1. Hotels have:
type ---> several defined types (beach hotel, ski hotel etc.)
facilities (which are devided in several groups(health&sport,recreation,information etc.))
E.g health&sport include pool,gym,suna etc.
Now, obviously there is many to many relationship between hotel types and facilities. One hotel can have(offer) many of these facilities and one facility could be offerd in many hotels.
The problem is that some types of hotels have extra/less facilities(amenities)). E.g the ski hotel type has all facilities(amenities) in health&sport group plus "ski rental".
How could I design the DB so that I know what amenities are allowed in which hotel types.
Or should I describe all possible amenities and look for restrictions on php level.
I was thinking maybe using SET column type instead of many-to-many relationship(I know it won't be normalized data that way but on the other hand I will save a lot of nasty SELECTs). Any suggestions?
2. This one is pretty easy. I have read about it but never implemented it, hence I am kinda confused.
How could I design self referencing relationship.
For example I have a "location" table.
Each location has an id. But each locatation could have 4 alternative locations. How could I implement this? Any pointers would be great.
Thank you all for spending time reading this post.
JD
Two Q regarding DB design - self referencing and...
Moderator: General Moderators
Re: Two Q regarding DB design - self referencing and...
Once you normalize this for use in a Relational DB you could end up withjmut wrote: 1. Hotels have:
type ---> several defined types (beach hotel, ski hotel etc.)
facilities (which are devided in several groups(health&sport,recreation,information etc.))
E.g health&sport include pool,gym,suna etc.
Now, obviously there is many to many relationship between hotel types and facilities. One hotel can have(offer) many of these facilities and one facility could be offerd in many hotels.
The problem is that some types of hotels have extra/less facilities(amenities)). E.g the ski hotel type has all facilities(amenities) in health&sport group plus "ski rental".
How could I design the DB so that I know what amenities are allowed in which hotel types.
[hoteltypes] 1 - n [hoteltypeamenities] n - 1 [amenities]
[hoteltypes] 1 - n [hotelamenitygroups] n - 1 [amenities]
[amenitygroups] 1 - n [amenities] (might be n - m that that needs normalization into 2 1 - n relationships too)
http://dev.mysql.com/doc/mysql/en/innod ... aints.htmljmut wrote: How could I design self referencing relationship.
For example I have a "location" table.
Each location has an id. But each locatation could have 4 alternative locations. How could I implement this? Any pointers would be great.
Re: Two Q regarding DB design - self referencing and...
You meantimvw wrote:Once you normalize this for use in a Relational DB you could end up withjmut wrote: 1. Hotels have:
type ---> several defined types (beach hotel, ski hotel etc.)
facilities (which are devided in several groups(health&sport,recreation,information etc.))
E.g health&sport include pool,gym,suna etc.
Now, obviously there is many to many relationship between hotel types and facilities. One hotel can have(offer) many of these facilities and one facility could be offerd in many hotels.
The problem is that some types of hotels have extra/less facilities(amenities)). E.g the ski hotel type has all facilities(amenities) in health&sport group plus "ski rental".
How could I design the DB so that I know what amenities are allowed in which hotel types.
[hoteltypes] 1 - n [hoteltypeamenities] n - 1 [amenities]
[hoteltypes] 1 - n [hotelamenitygroups] n - 1 [amenities]
[amenitygroups] 1 - n [amenities] (might be n - m that that needs normalization into 2 1 - n relationships too)
.....
[hoteltypes] 1 - n [hotelamenitygroups] n - 1 [amenitygroups]
right?
Or I missunderstood something - it becomes a "circle" relationship
Btw there will be no n-m relation. Any amenity belong to only one amenity group.
Re: Two Q regarding DB design - self referencing and...
Hi again 
I have on more fundamental question concerning this topic....
This database design so far...will cover all relations so that I know what checkboxes(menues) will be shown on each page...
e.g
For hotel type beach
Will have amenitygroups(taken from hotelamenitygorups table)... - all of them
Each of these groups will have amenities (taken from "hoteltypeamenities" - to know for the given hotel type what amenities we have). - all of them
Hence this info will be predefined and inserted in advance....so that I generate the structure(checkboxes) for the user to choosen from...
Then how could I know which boxes(amenities are chosen) clicked for the given hotel.
I will need another table for this? How it will be ralated in the givven situation.
Thanks in advance for any comments...
I have on more fundamental question concerning this topic....
This database design so far...will cover all relations so that I know what checkboxes(menues) will be shown on each page...
e.g
For hotel type beach
Will have amenitygroups(taken from hotelamenitygorups table)... - all of them
Each of these groups will have amenities (taken from "hoteltypeamenities" - to know for the given hotel type what amenities we have). - all of them
Hence this info will be predefined and inserted in advance....so that I generate the structure(checkboxes) for the user to choosen from...
Then how could I know which boxes(amenities are chosen) clicked for the given hotel.
I will need another table for this? How it will be ralated in the givven situation.
Thanks in advance for any comments...
As I see it - there are two stages.timvw wrote:Meaby i don't understand the problem..
Each amenity should have it's own amenity_id (= primary key).
Thus, if you use that value for the selections they can make, you know exactly which one they have choosen?
1. There is predefined data in the tables - this helps you to determin the menues/checkboxes to create....In this case its a little awkward because different hoteltypes(ski,beach etc) can have different amenities within the same amenitygroup. (e.g "ski" hotel type have "ski_something" amenity added to the "health&sport" amenity group, besides the other amenities in this group that all other hotel types have.)
So with the predefined data I have to clarify which amenities goes to which hoteltypes(just to draw the checkboxes).
2. On the other hand there is the actual results from the choosen check boxes -> the one that the user clicked.
Does this make any sense or this is completely wrong way to think of the design.
here's how I think I solved my problem..... 
[hotel] has:
[hoteltypes]
[hotelid]
Relations:
[hoteltypes] 1 - n [hoteltypeamenities] n - 1 [amenities]
[amenities] 1 - n [hotel_amenity] n - 1 [hotel]
[hoteltypes] 1 - n [hotel]
[amenitygroups] 1 - n [amenities]
[hotetypeamenities] will be predefined table that will show which amenities fall in to exactly which hoteltypes (I will use this to display the boxes)
On the other hand I will use [hotel_amenity] table to store the actual checkboxes the user clicked.
This way I present the logic that different hoteltypes actually have different amenitties withing given group.
Any comments on this......
P.S.
The other solution is to put all amenities in all hoteltypes(simple many-to-many relationship) and then with php just remove the one not applicable(from the array) before displaying the boxes.....
[hotel] has:
[hoteltypes]
[hotelid]
Relations:
[hoteltypes] 1 - n [hoteltypeamenities] n - 1 [amenities]
[amenities] 1 - n [hotel_amenity] n - 1 [hotel]
[hoteltypes] 1 - n [hotel]
[amenitygroups] 1 - n [amenities]
[hotetypeamenities] will be predefined table that will show which amenities fall in to exactly which hoteltypes (I will use this to display the boxes)
On the other hand I will use [hotel_amenity] table to store the actual checkboxes the user clicked.
This way I present the logic that different hoteltypes actually have different amenitties withing given group.
Any comments on this......
P.S.
The other solution is to put all amenities in all hoteltypes(simple many-to-many relationship) and then with php just remove the one not applicable(from the array) before displaying the boxes.....