CODEWITHSUNDEEP
database-design
Keeper
Keeper

Reputation: 960

Employee In/Out Table - how to store additional information?

I get records from multiple rfid hardware devices after an employee reads his rfid-card. These records contain

After reading this stackoverflow question there are two ways of designing the database:

Using the latter design would make reporting / analysis very easy (e.g. "Which employee worked longer than x hours?", "Which employee has no out record?", "Total hours per employee?",...).

Option 1

But as I need to store all the above information for in- and out-records, my table design would like like this:

RecordId | EmployeeId | TimestampIn | DstIn | UidIn | ProjectIdIn | TimestampOut | DstOut |...

This does not "look" like it's the right way to accomplish what I need.

Option 2

I then thought about using a second table for details:

Records:

RecordId | EmployeeId | TimestampIn | TimestampOut

RecordDetails

RecordDetailsId | RecordId | Timestamp | Dst | Uid | ProjectId

This way I can access the details if needed, but can do basic calculation directly on the records table.

Option 3

The third option would be using a event-table:

RecordId | EmployeeId | Timestamp | Dst | Uid | ProjectId | In/Out

This would definitely work, but it will be harder to analysis / reporting later (see above).

So my question comes down to this: Is any of the above designs considered "best practice" for my problem and should I go with one of the options? Although Option 1 looks good in a way to calculate durations etc. I fear that in case I need to add additional columns, this ends up in a mess.

Note: If a record is in/out will be determined by the last entry made by the employee. If it's a in record (Out is empty in option 1/2) then it will be an out entry.

Upvotes: 0

Views: 445

Answers (2)

Scott Allen
Scott Allen

Reputation: 533

Don't know how helpful this is, but I would go at it like this.

    Employee
    PK EmpId | Name | ect..

    EmpDevice
    PK EmpDeviceId | FK EmpId | FK DeviceId | ActiveFrom | ActiveTo

    Device
    PK DeviceId | FK EmpDeviceId | Name | Serial Number | ect..

    EmpProj
    PK EmpProjId | Fk ProjectId | Fk UserId | ActiveFrom | ActiveTo

    Event
    PK EventId | Fk DeviceId | Timestamp | Status

I'd have your Event table as small as possible, as this data will be repeated every time your employee signs in. Meaning everything else needs to be separated out.

  • Key to your device, then join in the employee and project if needed for queries.
  • 'Status' column which is a bit value, 1/0 representing in/out.
  • DST could be derived when needed.
  • Device assigned to an employee. If your employee changes device frequently then you could have a junction table 'EmpDevice'.
  • Same as above point for Project.

Upvotes: 1

Gilbert Le Blanc
Gilbert Le Blanc

Reputation: 51515

I would recommend using an event table. Event tables are so much easier to audit and troubleshoot.

RecordId | EmployeeId | DeviceID | Timestamp | Dst | Uid | ProjectId

In/Out is left off the event table. The in or out status is determined either by manual inspection of the event rows, or later processing.

Including the DeviceID allows for the manual insertion of event rows later, after it's determined that an in or out event was missed by an employee.

To take one example, how do you determine whether one employee's 8am to 7am is a last minute 23 hour in / out project push, or a missed out?

Upvotes: 0

Related Questions