Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Doing database design before you decide what database to use is somewhat like selecting drapes and furniture before buying a house. –  Leigh Riffel Mar 11 '13 at 19:43
    
I see your point. Though, I might counter with the fact that you wouldn't buy a house with stairs if you know there is going to be someone in it who needs a wheelchair. =). But, ultimately, the lack of DBMS choice is that the bigwigs want to see a functional prototype before they commit too much funding. But, yeah, it definitely helps to know your tools before trying to build a project. –  phobos51594 Mar 11 '13 at 20:14

2 Answers 2

up vote 1 down vote accepted

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

share|improve this answer
    
Wow, thanks for the extra info on SQL Fiddle. Looks like this is shaping to be a more intricate decision than I thought. –  phobos51594 Mar 12 '13 at 18:00

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.

share|improve this answer
    
I like this answer, however I think Leigh's breakdown better suits some of the requirements for this particular project. But I do think this is a good analysis, perhaps for a slightly different requirement set. Thanks for the good breakdown, though! I am going to keep this in my back pocket for next time I need it. –  phobos51594 Mar 11 '13 at 20:19
    
@Joel People are going to ask for a business rule like that regardless of the way it is implemented in the database. They could also ask for automatic check-in when a checked out item is checked out again or even an automatic check-in after a period of time. None of these designs prevents these, but in my mind #3 makes them easiest to add. –  Leigh Riffel Mar 11 '13 at 20:31
    
@Joel I'm not sure I see where you're going with the physical inventory process. What new value would your "inventory movement type" column have? –  Leigh Riffel Mar 11 '13 at 20:37
    
@LeighRiffel -If you have a type column in OP's option #2, then there would be a code for check-in and a code for check-out and you could easily add another code for inventory-confirmed. One of the reasons OP's option #2 is better than #3 is that checking in and out is very likely to going to include more information than just a date/time. There could be columns for who did it, where they did it, what terminal they did it from, what notes there were re item condition, etc. This gets to look a lot like 0NF when there is more than a date/time to be tracked with each step in the process. –  Joel Brown Mar 12 '13 at 1:18
    
@JoelBrown - I understand what you are saying now and agree that if you find yourself creating ColumnName2 you probably need to change the design. In this case, the concepts of In and Out are tightly knit and yet may require different fields (approval user for out / condition for in). As such #3 is both pragmatic and somewhat safe from 0NF. If there are multiple INs for a single Out, then option #1 might be a better choice. In the end, the best solution is highly dependent on what queries will be done. Thank you for pointing out the pitfall of #3. –  Leigh Riffel Mar 12 '13 at 15:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.