Need help with my reservation system

[Sketchin]

Hello,

I have a reservation system set up that includes a parent table with a child table. The parent table contains all of the information about the reservation, such as company name, date out, date in, contact name ect.. the child table contains information of what equipment is being reserved.

Currently it is working fine, but last week someone wanted to return 1 of their many items. My question is how do I handle this scenario in my database? I would rather not delete the item off the reservation because I obviously need the data for historical purposes and metrics tracking (we track equipment hours used and that is based on the reservation duration).

Another thing I could do is remove the returned item and put it on a new reservation, except that is sort of a duct tape fix. There must be a way to properly handle this scenario. Any advice is appreciated!

Thanks

 

[jdraw]

Do you have some concept along this line:

planned booking/usage vs actual booking/usage

If you review your data model, is it possible to have an unused or returned qty ....???

You must have had similar situations previously???

There's a generic reservation data model at
http://www.databaseanswers.org/data_models/generic_reservations/index.htm

 

[MarkK]

If your system only has Reservation and ReservationDetail objects, then equipment that has never been reserved doesn't exist, which is a design problem. You need a table for your equipment apart from any reservation. Then you can use a ReservationDetail object to join Equipment to a Reservation for a period of time. Where some companies sell quantities of stuff, you actually sell quantities of time, and the stuff comes back. ReservationDetail should have a fixed Reservation as a parent on one side, and a fixed piece of Equipment as a parent on the other side. And the ReservationDetail record specifies the quantity of time you are selling and the price per unit of that time. Then you can sell different quantities of time for different equipment for the same customer on the same reservation.

Does that make sense?

 

[Sketchin]

I have an equipment table aswell as a bill of material table. A collection of equipment is a BOM, and the BOM's go on the reservations.

An example of a BOM would be something like a laser scanner that someone would borrow for a period of time. If thats the only thing they reserved, and they return it early, I just simply change the dates. If they borrowed a couple things like a laser scanner and a GPS receiver, and returned one of them early, I would have to delete it off the resservation and create a new one with the new dates. As far as the metrics are concerned, this is fine, but I feel that there must be a proper way to do this.

Lagbolt: I think I see what you mean. Rather than track the dates of the reservation on the parent table...track it on the child table for each item? My application is huge, and basically finished, so making this change could take some work but I think its most likely the proper way to do it.

 

[Sketchin]

When I started this database application I was a beginner, so the concept of a data model was lost on me and still sort of is!

 

[jdraw]

Well in very general terms, a data model is much like a blueprint of your tables and relationships. If you start with the "business facts" that the database is intended to support, then you can identify the major subjects involved. For each of the major subjects you can identify the "attributes" (fields) that would be needed to receive/store data for use and reporting. Once you have a "model", you can test the model with test data from the business. The model either supports or does not support the business. Most models are not perfect, but strive to meet some "best practices" --Normalization, referential integrity...

It's never too late to learn/research data modeling.

Some links
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

http://xa.yimg.com/kq/groups/20697041/1514891951/name/UnderstandingNormalization.pdf

Good luck.

Can you post a jpg of your tables and relationships?

 

[Sketchin]

I looked at that data model you sent me in the second post and that is pretty much exactly how I do it.

I think the way to fix it is to move the dates requested to the item being reserved, that way I can have different return dates for each individual item rather than the entire reservation.

Going to be a huge PITA to re-do a bunch of queries and forms, but I think its the right way to do it.

Thanks for the advice guys!

 

[jdraw]

From one of the articles I sent links for

A solid database structure is the foundation of any successful database
application, and you will inevitably encounter problems if your database is poorly designed. Regardless of how you access your data, the information you retrieve is only as good as the data upon which it is based.

Get your structure right and the queries and forms should be easier. Let's hope.
Good luck.

 

[Sketchin]

Ya, I think for the most part I'm good. Its just that when you are brand new to access you can easily overlook small things like this that turn into big problems. Im sure there are some other flaws I just haven't found yet!