Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2010
    Posts
    2

    Unanswered: Database structure

    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.

    Thanks in advance,
    burnin240sx

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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.
    Paul

  3. #3
    Join Date
    Mar 2010
    Posts
    2
    That sounds just like what I need.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Post back if you get stuck, and welcome to the site by the way.
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •