Page 1 of 1

Relational database design question

Posted: Fri Nov 17, 2006 12:20 pm
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

Posted: Fri Nov 17, 2006 12:34 pm
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.

re

Posted: Fri Nov 17, 2006 1:05 pm
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

Posted: Fri Nov 17, 2006 1:11 pm
by feyd
Looks fine.

Posted: Sat Nov 18, 2006 3:13 pm
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