DB question(s) that I have never heard a direct answer for
Home > IT Answers > Database > DB question(s) that I have never heard a dire...
VirgilReality
20 pts.
0
Q:
DB question(s) that I have never heard a direct answer for
Database design, Database
In designing a database for use where I work, I ran smack-dab into a few old questions I have always had relating to relational databases.  I say "old" questions because I developed them a long time prior.  However, I think the answers are entirely relevant today (obviously, otherwise I wouldn't have run into them again).

Some of these questions rely on the acceptance of some basic point before getting to the point at hand.  If you detect a fault with the predetermined point, then by all means correct it for me.


I'm going to limit this to one question for now...


--Situation: you have designed a table into which you insert records representing occurrences of an event.  For example, a time clock application obviously would write events to a table in a DB related to people arriving and leaving for work.  The question arrives with the realization that one event logically relates to another substantially similar event at a different point in time.  Here, for instance, clocking out would seem to be subsequent to clocking in.  The reality is that sometimes people forget to clock in and clock out, but skip that obvious issue for a minute.  


In designing the table, two options come to mind.  First off, the design could be that a single record represents a basic work cycle, where there is a column for the time you clocked in and the time you clock out.  The second option is to treat each event as a distinct record, inserting a new record at clock-in and another at clock-out.


The single record multi-event option seems logical, and it would certianly make it easy to calculate the total time in that work-cycle.  However, it seems that to update an existing record runs a risk (albeit slight) of introducing an error...putting the clock-out in the clock-in column, for instance.


The two-record two-event model seems more streamlined and pure (from a geek's aesthetic perspective, anyway), and seems smarter because you're not editing records (and reducing opportunities for errors to be introduced).  It increases the difficulty in computing the amount of time in a work cycle, though, and that's a point that shouldn't be ignored.


So which is it?  What's the best choice?  What solutions have you seen before?


Thanks
ASKED: Sep 14 2009  10:03 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Kccrosser
1850 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
Well, the standard answer is DEPENDS. ;-)
+++++
I would go with the one record that contains IN/Out time.

Here are some of the DEPENDS???
1. At the point of capture, what information are you getting?
ID#, Job Number, TimeStamp, Time Indicator (In vs Out)
2. If the In time was not entered can both In and Out be entered at data collection point, or does part of the information have to come after the fact with a different entry?
3. What, if any, edit checking can be done at data collection point?
===

You must be able to link the records at entry time, else your wide open for all kinds of errors.
Like what if I did not clock out at 5pm and had to consecutive 8AM entries for continues days? Did I work 24 hours or am I missing an entry?

===
With the single record per task (In and Out), if I am missing one, it is easier to correct errors after the fact.



----------------

A thought -( I've seen both ways and think the one row per event is better. ) - how many in/out combinations will
be allowed per row? ANd you will need a date/time column for each event (for each in and for each out). I suggest the one row per event approach.

Steve


------------- kccrosser
I would also go with the one transaction type entry per row (i.e., transaction_datetime, transaction_type [in/out], person_id). When a person checks in, write a new row. When they check out, write a new row.

As noted, there are many opportunities for missed entries/exits. One transaction per row allows recording two (or more) successive transactions of the same type (in or out), and (IMHO) it is easier to write business logic to infer missing records than trying to reconcile bad data once written into a record.

The other advantage is that the transaction recording is much shorter and simpler: "insert into ..." suffices. With in+out fields in the same row, the recording transaction needs to check for and handle a variety of conditions: no existing record with an "in" but no "out"; record with "in" but no "out" exists, but the "in" date is earlier than a subsequent "in" record, etc.

I prefer to keep the data recording transactions short, simple, and trouble-free.
Last Answered: Sep 16 2009  7:49 PM GMT by Kccrosser   1850 pts.
Latest Contributors: Meandyou   1840 pts., CharlieBrowne   6540 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Carlosdl   29795 pts.  |   Sep 16 2009  8:36PM GMT

Yes, it depends.

But without more details, I would also tend to prefer the one row per event option.

The original question says “a table into which you insert records representing occurrences of an event“.

If you insert only one row containg the in and out times, I don’t think that row represents “an event”.

I also wanted to comment on this:

“However, it seems that to update an existing record runs a risk (albeit slight) of introducing an error…putting the clock-out in the clock-in column, for instance.”

It would also run the risk of putting a wrong time, or putting a wrong employee id, or writing the information to the wrong table, etc…

I assume this would be made by an application, so, once the application has been written, and it works ok, I don’t see this being an additional risk.

 

ASWDEVELOPER   205 pts.  |   Sep 22 2009  4:00PM GMT

one short addition to the discussion … i would like to see a short description
of how the records would be used … and within this context, how to handle
the consequences of events which are not recorded?

 

Juju   335 pts.  |   Sep 23 2009  3:36PM GMT

One table, definitely, but you could have separate input forms for in-time and out-time based on the same table. I designed a database to reconcile agency staff time worked, billed, paid and have run into some of the same problems you have. You can run a quick query to find out who failed to clock in or out and you can also make that field “mandatory” so that you know immediately if they fail to clock in/out.

Good luck on your project. I, too, and an older database user/designer going back to 1981 and Radio Shack’s Profile Plus database program.

Judy :)

 
0