I'd need to re-read this one when I have more time, but it sounds to me like you'd want to consider a pivot table (many-to-many) between Beds and People.
So assuming your Beds don't move between Houses, the relationships may be something like:
- A House has many Beds. And a Bed belongs to one House (one-to-many)
- A Person has many Beds. And a Bed has many People (many-to-many)
That leads us to consider the following tables:
- Beds
- Houses
- People
- Occupancies (Bed Person pivot table)
Then, in the Occupancies table, you would store the Related Bed and the Related Person, as well as the Check In (Datetime field) and Check Out (Datetime field). That'd set you up where you could have a summaries on Beds to determine their Availability (max Check In and Check Out datetimes).
Conversely a summary on the People table could show you where the Person is currently "Bedded."
You may want to consider a formula field to house the Status on the Occupancies table to leverage in conditional summaries. For example,
// Occupancies::Is Available (Checkbox) If ( not IsNull([Check Out]), true,
// Default to unchecked false)
Or maybe you'd need to another table to track Cleanings that relates to Occupancies too or another datetime on Occupancies. Anyway, if you delegate the Status or "helper checkboxes" logic to the pivot table, that may make summarizing a bit easier.
We're getting off topic a bit, but hopefully, that gives you some ideas!
Quickbase is great for rapidly prototyping, so sometimes, I'll build a separate "playground QB app" using the free QB Builder Program account and try various data modeling and different ideas. Then, once dialed in, build out in the "production QB app."
It's important to really try to poke holes in your model. Try "searching for the nouns" and avoid overriding data if you need to retain history, as it seems like the case here. Maybe study hotel booking systems too as that seems similar!
------------------------------
Brian Seymour
------------------------------
