Many crazy - assign person to beds

[Maarkr]

Need suggestions to assign a person to a bed for a specific date. Some rooms
have one bed and some have many. How can I get this so I can assign a person
to a specific bed and deconflict the dates?

I have many buildings, (one to many) to many rooms, (one to many) to many
beds, to a linking table (beds to people) linking the people to a bed.

RoomID Rooms Table
fkBldgID
RoomNo
NoOfBeds

BedID Beds Table
fkRoomID
BedNbr

Bed2PeepID Link Table for beds to people
BedID
PeepID
ArriveDate
LvDate

PeepID People Table
FName
LName
etc

thanks as always...if I have enough info here to solve this...

 

[FredFred]

Do you need to track building and rooms (for it's own sake or to provide
unique identificaton of beds?) I'm assuming yes,

I'm also assuming that there is other info for each person, which is a
reason to have them in a separate table vs. just entering their names.

If your need is only to track which person is CURRENTLY assigned to which
bed , then your design that you have mind will work. Just link them as you
are implying. Your main entry is entering a person for the bed in the bed
table. . And, if so, there are no conflicts between dates. There is just
data / date for the one current occupant of the bed.

More likely what you need to record is all instances of a person being
assigned to a bed e.g. including start date, projected end date, end date
that actually occurred (or filed that says whether or not this instance is a
current occupancy) . For example, that Joe Blow occupied bed #4657
12/1/06-12/2/-06 would be a record in that table that stays there forever.
This would be another table in-between the people and bed tables. You can
then extract what you need using queries for form and reports (e.g. to only
look at current occupancies)