Designing inventory tracking mechanism

[Pawtang]

I'm in the early design stage for a small inventory-tracking application. I know there's an abundance of examples for your classic transaction table model, as well as for the asset tracking model where you change the location/other qualities of specific items; I'm stuck on a specific design element somewhere between the two methods.



We have a variety of training items that are lent out to customers, so we want to track the status of each individually. They fit into categories; there are X amount of trainer A, Y amount of trainer B. I'm giving them each an individual ID so they can be specifically tracked, rather than just as a sum.

Currently, I have just one table that is a list of trainerID and trainerType. I'm considering adding a field for status that is either Checked In or Checked Out, but what I'd prefer to do is treat this like a transaction so that I can record a bunch of supplemental information along with the check-out and check-in. But I also want the record to have a persistent state of checked-out or checked-in until the state is changed. Would the best way to do this be:

Two separate tables; one is just a list of trainersID and trainerType; second table is transactions. Determine status of trainer on the fly as needed with query by checking last transaction for the trainerID. When checking in, check to see if last transaction was check-out. When checking out, check to see that last transaction was check-in.

I think that would work fine, seems a bit clunky though and wondering if there's any easier way.

[pbaldy]

You'll probably get different opinions, as there are a number of ways to do this. I won't take a position on best, but I'd probably have your two-table setup, with a tweak. My transaction table would have fields for the date (or date/time) the item was checked out and the date or date/time it was checked back in (along with who has it, etc). I wouldn't save the status of each. A query of the transactions where the checked back in field is null tells you what's out; everything else is available (unmatched query against the first table).

[Pawtang]

You'll probably get different opinions, as there are a number of ways to do this. I won't take a position on best, but I'd probably have your two-table setup, with a tweak. My transaction table would have fields for the date (or date/time) the item was checked out and the date or date/time it was checked back in (along with who has it, etc). I wouldn't save the status of each. A query of the transactions where the checked back in field is null tells you what's out; everything else is available (unmatched query against the first table).

To me, it seems like this method would only work for a single check-out of an item. If an item is checked in and out several times on different dates, the checked back in will not be null but it wont necessarily represent the current state.

I'm thinking the ideal way might be to have a field in the tblTrainers records that is a semi-persistent state of Checked In or Out. Then the transactions table records the date and supplementary data every time the state is changed. That seems like a clean way to execute it from a table standpoint, but I'm not sure how I'd make this happen through the queries and forms - maybe just a main form where you search for the trainer by its name and toggle the status, and a subform that records transaction details?

[orange]

Sounds a lot like a library. You borrow 1 or many books and return them. The transaction is for the individual book, but nothing wrong with multiple transactions on same or different dates.

[Pawtang]

Sounds a lot like a library. You borrow 1 or many books and return them. The transaction is for the individual book, but nothing wrong with multiple transactions on same or different dates.

Good call. Looking at lending library template. Little more than we need but should get the job done.

[pbaldy]

To me, it seems like this method would only work for a single check-out of an item. If an item is checked in and out several times on different dates, the checked back in will not be null but it wont necessarily represent the current state.

I have that method in place for drivers taking out vehicles (limousines, taxis). It has been in place for years, each vehicle has probably been taken out hundreds if not thousands of times. Maybe I didn't describe it well enough.