I work for a decent sized company in the hardware sector. Currently, we have a number of varying assets that need to be tracked. Until recently, this was done by hand as was an (obviously) error-prone practice. We are now (finally) putting this into a proper RDBMS, though the exact DBMS has not yet been selected.
Thankfully, each asset is already tracked by a unique asset tag / number.
I am trying to figure out which is the best way to track check-outs and check-ins. I have done some googling on the matter, but I cannot seem to find what I am looking for. I simply need to track when a given asset was checked out and subsequently checked back in. Currently, I am considering three primary options:
One table each for check-ins and check-outs. This has the benefit of cleanly separating the two concepts and avoiding any NULL values. However, it does make the queries for such information more complicated.
One table for both check-ins and check-outs; check-in and check-out on different rows. This allows me to reuse the same table, but it will require a lot of self-joining to get anything. I also don't like the reliance on some kind of constant to differentiate between 'out' and 'in' records.
One table for both check-ins and check-outs; check-in and check-out on the SAME row. This is my current favorite as it makes querying trivial and marries the two concepts ('out' and subsequent return) in a very understandable manner. My only hesitation with this is that it relies on NULL to determine if an asset is still checked out or not. It has been my experience that NULL can be a harsh mistress, especially when the design is DBMS-independent.
I am aware that a lot of this may depend on my particular requirements; however this problem seems common enough that I am hoping for some level of expected best-practice and WHY that practice was chosen.
Too long; Didn't read
Which RDBMS-agnostic design technique is best for tracking check-outs and check-ins of assets and WHY:
- Two tables; One each for check-outs and check-ins
- One table for both; Different rows for' out' vs. 'in'
- One table for both; 'out' and 'in' on same record
Thanks in advance for any help.