Help needed for tracking inventory of rental equipment

[gilles007]

Hi!

I'm new to db and php but have a pgming bckground.

I need help/pointers with developing a simple system to track the inventory of audio equipment rental. Can anyone provide some tips on the logic I would use to accomplish this?

What makes it challenging is the inventory is date dependant. That is, I need the ability to query "on such and such date, how many microphones will be on hand?".

I know this is a tough question to answer but any help will be appreciated.

Thanks,

GIlles

[laserlight]

I suggest reading the book Developing Time-Oriented Database Applications in SQL by by Richard T. Snodgrass and Christian S. Jensen

I found it to be an excellent book on designing databases that deal with such temporal data.

[gilles007]

Thanks Laserlight,

I'm trying to speed things up by not having to read an entire book. Perhaps the truth is that this project is bigger than I thought...

Gilles

[Sxooter]

Do your date maths in the database. That's my first hint. If your database doesn't do date math well, then use a different database that does.

Newer versions of MySQL are pretty good, PostgreSQL is excellent.

Find your date truncation functions. In postgresql it's date_trunc, and this lets you truncate a date down to a certain field. I.e. hours, minutes, days, seconds, etc...

2006-01-12 12:13:34 date trunced to day becomes 2006-01-12 00:00:00 and so on.

Use ranges. Where datefield between '2006-01-01 00:00:00' and '2006-01-07 23:59:59'

study and use time zones if needed. Generally you're better off using them from the start so that when you need to operate across time zones you're ready.

And, in your spare time, read that book, eh?

[gilles007]

Sxcooter...

Thanks a lot for your input. This sets me ahead of where I was. Good tips. I must now figure out the logic. My first time with such a temporal project. Great learning.

Thanks again!
G.