I am setting up a database to keep track of people, phone info and the rooms that belong to them. I am having trouble thinking about how I should go about setting up the rooms table as some of the people have an office and other rooms for labs. In the end I would like to be able to run a query that tells me which rooms are available when new faculty come into the department.
I currently have setup 3 tables.
1. person and information associated with them.
2. Phone numbers, people who have numbers assigned to them. ( need to know if numbers are free to give to new faculty)
3. Rooms and the people who are in these rooms. The issue here is that some rooms have multiple staff/faculty in them)
I was thinking that I could have the 3rd table have rooms, occupant 1, occupant 2, occupant 3. etc.. but I think that is a sloppy way of handling the issue. the other way is to have the rooms listed in the people table but I don't like that idea either.
I'm hoping the some one may have a great idea for my issue.
It sounds like what you need is what's often called a many-to-many junction table. A person can be in many rooms, and a room can have many people. That table would have fields for room ID and person ID. If I'm in 3 rooms, I have 3 records in that table.