how to track assets

[madman411]

Hi All

 

My client would like me to implement a tracking system for their cine cameras. Basically, our rental agents need book cine-style cameras by the particular asset id (mainly to keep track that all consigned cameras are sent out equally). Their current booking system will just book them at a SKU level, not taking in to account which camera has been sent out more times than another. Currently they have all their cameras in an excel spreadsheet with the days running horizontally. They fill each day in a certain color to show which camera is out and for the length of time it is out for (a date range). It gives them a chart view which is easy to read. The irritating part is that it is so manual.

 

Here's what I want to do, but don't quite grasp how.

 

The user opens up the particular window in which this part of the system tracks the cameras. The camera assets are already contained within the Asset ID table. The camera type, or SKU, is in the Product ID table.

 

The user can select the model camera they want to book (via selecting or entering the SKU number that particular camera is) and then entering a date range. This will display all camera assets that are that particular model that have not been marked as booked (or "out") for that particular time frame. Or, they could simply view a chart to see what is available.

 

My issue is how to implement this system. The assets and product SKUs are already in the database within their respective tables, but contain all the company's inventory - not just the cameras. Would a separate table, called "bookings", perhaps, help put these tables together to manage the tracking? Viewing the asset availability within a bar chart would also be ideal for a visual reference.

 

Thoughts/comments always appreciated while I try to figure this out. The system I have is already in use tracking the assets in different ways. I'm looking to integrate this into the existing system.

 

Cheers!

[Alex Taylor]

You would definitely need a bookings table to track the actual bookings as they occur. A good rule of thumb when designing databases is that for every "entity' you wish to track, you need a separate table. So your Products are one kind of entity, the Cameras are another (since each camera may be the same product, but still a unique item in itself), and Bookings are yet another, given that each Booking consists of a specific Camera being loaned out for a specific date range.

 

In terms of implementing this, when your user is creating a new booking, you could design the interface so that they first enter a SKU number (or search for the product name), and that returns a list of all the Cameras related to that Product record by SKU. When they select the camera, that creates a relationship between the Bookings table and the Camera table, and indirectly the Products table.

 

When you have this structure set up, you should be able to look at it from both ways: looking at all your Bookings to see which specific Cameras are loaned out, and looking at a specific Product to see how many individual units (Cameras) are available, and which ones are currently booked.

 

Hope that helps!