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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jmut
Forum Regular
Posts: 945
Joined: Tue Jul 05, 2005 3:54 am
Location: Sofia, Bulgaria
Contact:

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

Post 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
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

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

Post 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
jmut
Forum Regular
Posts: 945
Joined: Tue Jul 05, 2005 3:54 am
Location: Sofia, Bulgaria
Contact:

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

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Yes, it was a typo.
jmut
Forum Regular
Posts: 945
Joined: Tue Jul 05, 2005 3:54 am
Location: Sofia, Bulgaria
Contact:

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

Post 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...
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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?
jmut
Forum Regular
Posts: 945
Joined: Tue Jul 05, 2005 3:54 am
Location: Sofia, Bulgaria
Contact:

Post 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.
jmut
Forum Regular
Posts: 945
Joined: Tue Jul 05, 2005 3:54 am
Location: Sofia, Bulgaria
Contact:

Post 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.....
jmut
Forum Regular
Posts: 945
Joined: Tue Jul 05, 2005 3:54 am
Location: Sofia, Bulgaria
Contact:

Post 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
Post Reply