Page 1 of 1

Two Q regarding DB design - self referencing and...

Posted: Sun Jul 31, 2005 4:30 pm
by jmut
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

Re: Two Q regarding DB design - self referencing and...

Posted: Sun Jul 31, 2005 6:07 pm
by timvw
jmut 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.
Once you normalize this for use in a Relational DB you could end up with

[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)

jmut 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.
http://dev.mysql.com/doc/mysql/en/innod ... aints.html

Re: Two Q regarding DB design - self referencing and...

Posted: Mon Aug 01, 2005 2:13 am
by jmut
timvw wrote:
jmut 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.
Once you normalize this for use in a Relational DB you could end up with

[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)

.....
You mean

[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.

Posted: Mon Aug 01, 2005 5:16 am
by timvw
Yes, it was a typo.

Re: Two Q regarding DB design - self referencing and...

Posted: Mon Aug 08, 2005 8:30 am
by jmut
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...

Posted: Mon Aug 08, 2005 12:34 pm
by timvw
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?

Posted: Mon Aug 08, 2005 2:58 pm
by jmut
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?
As I see it - there are two stages.
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.

Posted: Tue Aug 09, 2005 1:51 pm
by jmut
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.....

Posted: Wed Aug 10, 2005 9:40 am
by jmut
will some say if this is ok or completely wrong way to do it.
Does it make any sense...or did I not explained what I am trying to achieve...
10x