Relational database design question

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
ant_sutton
Forum Commoner
Posts: 32
Joined: Thu May 05, 2005 5:27 am

Relational database design question

Post by ant_sutton »

Hi guys.

I have a relational database that is part of a lecturer timetable system. One of the tables is RoomInfo. It contains a primary key Room code, capacity, location, type, and facilities.

It's the facility field that is causing a problem. The system lets lecturers request a room to teach in with multiple facilities from a web form. For example, a lecturer requests a room with a blackboard, whiteboard and computer point. I obviously don't want these three facilities input into the same cell as this will cause problems with using the data.

I really cant think of another way to do this though. If I use a diferent field for each facility a room may have, and the cell values will be yes or no this will leave alot of useless, unused cells in the database.

Can anyone suggest a good way to get around this problem (If you understand the problem. If you do not, I can elaborate :)

Thanks alot for any help

Anthony
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Build a separate table for facilities. Build a table that contains a reference a room (ID) and also a reference to a facility (ID). This is called a linking table.

The relationship you've described between rooms and facilities is a many-to-many relationship. A room can have many facilities. A facility can be in many rooms.
ant_sutton
Forum Commoner
Posts: 32
Joined: Thu May 05, 2005 5:27 am

re

Post by ant_sutton »

Hi. thanks for your reply. very helpful.

I now have :

Rooms:
Room_id (Primary key)
Capacity
Location
Type

Facilities:
Facility_id
Facility

Room_Facility:
facility_id (primary)
room_id (primary)

Would this work?

thanks alot

Anthony
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Looks fine.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post by califdon »

Yes, that should work just fine. Do yourself a favor and read up some on relational database structures. There are lots of good tutorials online. Google: database relational tutorial.
:D
Post Reply