Design Suggestions for Time-Based DB

[Jusfire]

I would like to design a db to track hours spent on projects. The one question I have is how best to design it so that staff cannot have multiple "timecards" for a period or more importantly overlapping times. Any suggestions would be helpful.
Basically it's a list of who's worked on what and when.

 

[ThomVF]

Create a table/entity for "employee"
Give that table a unique key on Employee-ID (or name) so
that an employee cannot exist twice

Create another table/entity for "project"
Give that table a unique key on Project-ID so a project cannot exist twice

Create another table/entity for "time worked"
This table will look something like this:
Time-Worked-ID INTEGER # Unique, Surrogate PKEY
Employee-ID INTEGER # FKey to employee
Project-ID INTEGER # Fkey to Project
StartTimestamp DATETIME
StopTimestamp DATETIME

If employees enter time-slots for a particular project, I am guessing that, so long as they do not overlap, they can enter in just about any combination of time slots and projects for any given day. Given that, an employee could enter more than one time period for a given project on a given date. For example:

Mon 7/15 Marketing-Proj 8am-10am
Mon 7/15 Sales-Proj 11am-3pm
Mon 7/15 Marketing-Proj 3:15pm-5:15pm

Tue 7/16 Sales-Proj 8:30am-3:15pm
Tue 7/16 Admin-Proj 3:30pm-5:45pm

I do not think database design alone can solve it. You may need to develop some code to enforce the business rules. This is because some sort of date function needs to be performed to check for overlaps.

If you are using ORACLE, I suggest a stored procedure called from your application or a pre-Insert trigger to perform this validation as the entry is submitted. You could convert the start and stop times into the number of seconds since some Epoch then, using simple operators (<=, >=) check if any other entry -for that employee- overlaps these numbers. Converting to seconds handles the entire spectrum of date-part checking (year, month, day).

Tom

 

[olded]

Hi:

Earlier in my career, I developed Time & Attendance software so I do have
definite opinions on this subject:

Above, Tom has given you a good, basic design. I definitely agree with
his comment about code to enforce the business rules. You really need
the added flexibility of client code such as VB, C, etc.

Allow me to add other fields you might find useful:

Time Card Table:
Time-Worked-ID INTEGER # Unique, Surrogate PKEY
Employee-ID INTEGER # FKey to employee
Project-ID INTEGER # Fkey to Project
StartTimestamp DATETIME
StopTimestamp DATETIME
timeworked Decimal
shift CHAR(1) # Fkey to shift table
paydate DATE
sys_adjust Decimal

New Field Discussion

1) shift: Typically, an employee starts working around the same time.
Entering a shift can default the StartTimestamp. Save data entry time.

2) timeworked: Generally, this is StartTime - StopTime. The database
purests will say you can compute it. True, but experience proved to me
that it was always nice to have this computed as soon as the StartTime
and StopTime were available.

Also, consider having data entry person enter the StartTime and the number of
hours worked, and have the system determine the StopTime. Generally, people hate
having to type in datetime strings.

3) paydate: For a first shift person working 8 to 5, the paydate is,
obviously, the date worked. But what about 3rd shift peope working 2300
to 0700. The start date is different from the end date. You may want
to compute a paydate different from the startdate.

4) sys_adjust: This is an adjustment is added/subtracted to the
timeworked for extraordinary occurrences such as working 2300 to 0700
during a daylight savings time change.

Epoch discussion

Tom's point about determining time from the EPOCH is valid. However, my
database of choice, Informix, has a datetime data type which allows
easy manipulation of the type. I simply subtract the StopTime from
the StartTime to get my interval. Obviously, it's not an ANSI standard,
but it sure simplifies this problem.

Good Luck with your project!

Ed

 

[ThomVF]

Jusfire: You are getting your money's worth with Ed's additional comments and his experience building these systems. I will make just a few more observations.

As far a storing the computed difference between start and stop, that's an artistic decision you will have to make. For example, if you decide not to store &quot;timeworked&quot; your report logic will have to compute it. I am one of those purists who believe in only storing the minimum and deriving other values as needed. It's a local decision you'll have to make - I've gone against my instinct at times because of the local requirements.

Not sure which database you are using, but as Ed said, most of them (ORACLE included) allow you to easily subtract date/timestamps to get the difference.

The Epoch was more of a logical explanation and you could probably just use logic as follows to check for overlaps:
Find any record for this employee
where start timestamp <= current start timestamp
and stop timestamp >= current start timestamp

If this logic yields data, you have an overlap. No entry for this employee can start between the start-stop period of another entry.

Tom

 

[Jusfire]

Tom and Ed
I appreciate your suggestions. To answer your question Tom...I'll be using Access2K. Additionally, I'd like to say that I like the idea of not storing stuff you don't immediately require. As for the sys-adjust field, that's a great idea. I've always wondered what the best solution was to compensate automatically for those 'odd' days.

Would the overlapping validation best be done with VBA and how so...my idea would be for something that looks for the same employee, same date, and then verifies that the times don't fall between some other. Is that basically correct? Any sample code would help me get the right idea.