Jump to content

how can I make sure a field doesn't go over a total..?


This topic is 5684 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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 ???

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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..

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 5684 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.