Extensible Asset Database Schema

Question

Background

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.

Question

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:

1. 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.

2. 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.

3. 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.

Answer 1

Option #3

NULLs are your friend. After you select the platform, study how NULLs work for the platform and embrace them. In this case they accurately reflect the absence of a check-in. In Oracle NULLS are not indexed, so you could create a function based index that swaps the NULL state giving you a very small index that contains only the entries not checked-in.

Option #1 would need a join anytime you want the Check-In date or status. Joins are your friend too, but in this case there isn't a benefit to separating this data unless there are times when a single checkout can produce multiple checkins. Option #2 requires repeating data or even more NULLS than option #3. Here are the three concepts fleshed out to tables columns and data.

#1
CheckOut
   Asset CheckInOut User DateTime
   1     1          1    3/10/2013
   2     2          1    3/10/2013
   3     3          2    3/11/2013

CheckIn
   CheckInOut DateTime   
   1            3/11/2013

#2
CheckInOut
   Asset CheckInOut User DateTime   InOrOut
   1     1          1    3/10/2013  O
   2     2          1    3/10/2013  O
   1     1          1    3/11/2013  I   
   3     3          2    3/11/2013  O

#3
CheckOut
   Asset CheckInOut User OutDateTime InDateTime
   1     1          1    3/10/2013   3/11/2013
   2     2          1    3/10/2013
   3     3          2    3/11/2013

SQL Fiddle's showing Option #2 and Option #3 with the additional requirements Joel Brown added and how various questions would have to be answered in SQL.

#2 - http://www.sqlfiddle.com/#!4/55e72/25

#3 - http://www.sqlfiddle.com/#!4/e2f27/6

Answer 2

You have to decide whether you want your world to be simple for the programmer or useful for the user.

The most useful scenario is the one which treats things that happen in the real world most directly. In your case that is your second scenario: One table for check-ins and check-outs with different rows for each.

The reason that this scenario is the most like the real-world is that it is modeling inventory events.

Each row in your table records the physical processing of a piece of equipment by someone. You are either taking it out or puting it back. There are no null columns to have to work around. What is problematic with this approach is that outs and ins don't necessarily come in nice, tidy pairs. However, this is very likely going to be a fact in your actual operations. In the real world, paperwork sometimes goes missing or people get sloppy with procedures. If you build your system along the lines of your option #3, then at some point or another someone is going to ask for a business rule that says "don't check out things that aren't checked in". While this makes good sense in a textbook sample problem, it will just be a nuisance in a real-world application.

This approach is also the most useful when it comes time to expand the requirements of your system. For example, let's say phase 2 of your system is to implement a physical inventory process. In your option #1 you need a whole new table. In your option #3 you need either another table or a new column. In option #2 you just need a new value for your "inventory movement type" column. Modeling close to the real world generally makes your database schema more future proof.

Track all of your events as individual records and then build your application logic to interpret the event stream.