help with design for a rooming database

[ferrisj]

I am trying to design a database where I can assigned people as roommmates. I found a similiar thread at

http://www.tek-tips.com/viewthread.cfm?qid=866707

I am not quite sure how to design this though. I will have people registered in a registration table. Then do I make a Rooms tables, where rooms would be identified as an auto number field? how do i assign the registrants to a room?

i want to be able to run a report that shows everyone in room 1, everyone in room 2, etc.

Any advice on this design would be helpful.

 

[lespaul]

Well you have people who are registered for rooms. Hopefully this is a temporary arrangement, so you will need to know when each person is done with the room?

I would first suggest you read The Fundamentals of Relational Database Design. Then maybe this model:

People
PersonID (PK)
FirstName
LastName
Address
Other people information

Rooms
RoomID (PK)
RoomLocation
MaxOccupancy

PeopleRegistratation
RegistrationID (PK)
PersonID (FK)
RoomID (FK)
StartDate
EndDate

You may need to further break it down to which bed they are assigned. You would then need to add a Bed table and use that in people registration instead of the RoomID because the BedID can tell you which room you're in:

People
PersonID (PK)
FirstName
LastName
Address
Other people information

Rooms
RoomID (PK)
RoomLocation
MaxOccupancy

Beds
BedID (PK)
RoomID (FK)
BedSize

PeopleRegistratation
RegistrationID (PK)
PersonID (FK)
BedID (FK)
StartDate
EndDate

HTH



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[johnherman]

If I had a dollar for every time lespaul said "suggest you read The Fundamentals of Relational Database Design", I could take a week off w/o pay and be ahead financially, too.

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[lespaul]

I wish I got a dollar each time I said "suggest...."!!!! But it's a GREAT introduction and I am so tired of trying to figure out non-normalized table structures in order to help people around here that I try as hard as possible to point the beginners in the right direction so when they come back in six months trying to extract some complicated query, it's easier on ME to help them!!!

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[willir]

Way cool Leslie. I wish I had a dollar too.

In addition to normalizing databases, the other problem some people have is identifying when they have a many-to-many relationship instead of a one-to-many.

Oh yea, and including spaces and special characters in their field names / table names.

I keep thinking about writing an FAQ or two but others have walke the road ahead of me.

Richard