Proper design for check in/out table

[Harlan]

I am creating an equipment tracking db.
Equipment can be checked in and out from the storage location (could be many
times per day/month/year). I want to maintain a historical record of all
uses of this equipment.

What is the best approach (advantages/disadvantages) for the table used to
record the check in and check out dates?

I have the following fields
EquipmentID
DateOut
DateIn

Two thoughts:

When the equipment is returned, search for the record where the DateIn is
null for the respective EquipmentID and fill in that date, thus having only
one record for each use of the equipment. This way, to "see" what equipment
is out, just search for null values in the datein field.

OR

When the equipment is checked out, have one record of it's checkout, and
then one record of it's checkin when returned. This way, to "see" what is
out, need to compare last dateout to last datein fields to see if equipment
is out.

With 4000 pieces of equipment being moved in and out, this table could
become fairly large over time. And will have to be queried constantly by the
other functions of the db.

 

[Armen Stein]

I am creating an equipment tracking db.
Equipment can be checked in and out from the storage location (could be many
times per day/month/year). I want to maintain a historical record of all
uses of this equipment.

What is the best approach (advantages/disadvantages) for the table used to
record the check in and check out dates?

I have the following fields
EquipmentID
DateOut
DateIn

Two thoughts:

When the equipment is returned, search for the record where the DateIn is
null for the respective EquipmentID and fill in that date, thus having only
one record for each use of the equipment. This way, to "see" what equipment
is out, just search for null values in the datein field.

OR

When the equipment is checked out, have one record of it's checkout, and
then one record of it's checkin when returned. This way, to "see" what is
out, need to compare last dateout to last datein fields to see if equipment
is out.

With 4000 pieces of equipment being moved in and out, this table could
become fairly large over time. And will have to be queried constantly by the
other functions of the db.

Hi Harlan,

The first method (1-record) is more efficient, since it will use fewer
records overall. Querying for equipment status will be a bit easier
also.

However, it might be a bit tricky to handle cases where you didn't
capture the checkout event, or the equipment never returned. You may
want to have other flags in the record that indicate that
a) the checkout date for the equipment was not recorded, or
b) the equipment was lost or destroyed, so although there is no check-in
date, the equipment is not really "checked out".

Hope this helps,

 

[fofa]

Your first approach usually works the best. Just index the fields you
will be seraching on. Also make sure you store the time with the date.