I'm starting to think about ways I can build a building occupancy tracking system.

Basically, I have 3 tables:

People

Floors

Leases

On each floor record, I have a field that indicates the number of lab modules on that floor.

Each lease record is a join between people and floors..

so it has the following fields:

person_id

floor_id

start_date

end_date

number_modules

I have 2 questions..

How can I make it so that the system will prevent the user from creating a lease that utilizes more modules than exist on that floor ?

Eventually, I will create a crude calendar layout that displays all the leases for each floor at various points in time.

So... I'd like to also make sure that the lease doesn't exceed the number of available modules at any point in time in the future.

Could anyone offer advice on how you might approach this ?

thanks ???

One way would be to create a table for the modules, identify each module with a serial number, and record which modules are leased. Your structure then would be:

People <-> Leases <-> LeaseItems <-> Modules <-> Floors

Leases:

lease_id

person_id

start_date

end_date

LeaseItems:

lease_id

module_id

Somewhat similar to invoicing, when a customer buys several items from the Products table on a single invoice.

Thanks comment,

I did think about that.. but for various reasons..

I'd rather not keep track of which modules someone is assigned to..

i'm much rather hoping to just have the lease specify the # of modules, the floor, and the date range..

and when a new lease is created... if the new lease is impossible to fulfill because at some point in the lease there are not enough modules on the floor, then the lease will not be allowed to be booked..

I can do this in theory for one point in time.. but checking the entire range of dates for conflicts is what is throwing me..

attached is a very crude first attempt at this..

room_tracking_beta.fp7.zip

p.s. the reasons i don't want to track the modules are:

1. my boss will be doing the lease booking.. and i want to make the experience as quick as possible for her

2. the building occupancy is very fluid.. and we often will really not know which actual modules the person will end up getting

3. a lease may include several modules so I would like it to just include the date range, floor, and number of modules

p.s. in the end I hope to build an interface that will show booked space and free space over time

This is also possible, but much depends on what kind of interface you want to build for your boss:

1. Will she be entering a new lease from the People table or from the Floors table?

2. Will she be entering directly into a portal, or will you give her some global fields to draft the new lease, then run a script to create a new record in Leases?

1. she'll be creating new leases for new people and modifying existing leases to accommodate expansion

2. i think global fields will be the way to go to do some conflict checking before everything is submitted.. but i certainly would love to hear your opinion

This is actually more complex than I have noticed at the start: you really need some sort of a timeline, to separate overlapping leases from consecutive ones.

I believe you could use the solution proposed here as the basis for your own.

Just to comment on the thread you linked to. I now have everything working nicely but i am curently working on finetuning the sulotion. And to make things even more complicated an article can now be booked to a certain minute on one date so instead of just checking dates i also have to check minutes and hours (time).

If anyone want's i can post the searches in the scripts that finds the relevant posts. Well my way of finding them (probably not the best way).

p

that would be great.. thanks.. I'm still struggling with this.