I have a general database design question.

[demopro]

I am trying to create a time keeping system and need to ask how to design a table. I created a table called timecard with these fields: Day1 = firstday of the two week pay period, pped = pay period, typhr = type of hour (sick, vacation...)

Auto_id (PK) int
Emp_id (FK) int
Pped datetime(mm/dd/yyyy)
Day1 float
Typhr int
day2 float
typhr int
day3 float
typhr int
day4 float
typhr int
day5 float
typhr int
day6 float
typhr int
day7 float
typhr int
day8 float
typhr int
day9 float
typhr int
day10 float
typhr int
day11 float
typhr int
day12 float
typhr int
day13 float
typhr int
day14 float
typhr int
certified_by int
cert_date datetime(mm/dd/yyyy)
modified_by int
mod_date datetime(mm/dd/yyyy)


This table layout would work if there are no multiply times for a single day. Example: on day1 I worked 5 hrs of regular time and 3 hours of sick time. How do I account for the 2nd line (3 hours of sick time) or more lines per day?

 

[RiverGuy]

Off the top of my head, I would first create a PayPeriods table with the following columns:

PayPeriodID INT/IDENTITY/PK
PayPeriodStart DATETIME
PayPeriodEnd DATETIME

Then, create a PayTypes table:

PayTypeID INT/IDENTITY/PK
PayTypeDescription VARCHAR

Then, your "time clock" table:

EmployeeID FK
PayPeriodID FK
PayTypeID FK
ClockInDateTime
ClockOutDateTime
--...Other Columns

This would work even if someone clocked in three or four times in the same day.

 

[gmmastros]

Before you go any further, I strongly encourage you to spend 30 minutes (or so) reading about [google]database normalization[/google]

It will change your perspective with regards to database design. Many of the concepts you encounter may feel awkward for you, but that's your problem (not the computer's). By properly designing a table, you will get a much cleaner table layout, and it will (probably) perform much better for you.

In this particular case, I would...

Auto_Id Primary Key Integer identity
EmpId Int (foreign key)
StartTime DateTime
EndTime DateTime
TypeHr Int (this should be a foreign key to a lookup table)

This way, you can have multiple rows per employee per day. It may make the reports a little more difficult to write, but you will have more flexibility in the way you store your data.

You may want to create another table the identifies the 2-week pay periods. Something like....

PayPeriod
---------
PeriodId Int Primary Key Identity
StartDate DateTime
EndDate DateTime

This will make your reports easier to generate.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[demopro]

Thanks for your help. I was looking to deep at this and had the correct answer all along. I was looking at this as a roll up on time for the day when by using the typhr column it is a detail view.

Thanks again.