Database Table Design - Primary Key

Question

I have a question related to Database Design. Your inputs/suggestions will be greatly appreciated.

We have designed a Timesheet Table which tracks employees hours spent on various projects. We have split the Timesheet Data into 2 Tables => Timesheet & Timesheet Detail. The Table structure for the Timesheet Table is :

CREATE TABLE [dbo].[TIMESHEET](
    [TIMESHEET_ID] [int] IDENTITY(1,1) NOT NULL,
    [EMPLOYEE_ID] [varchar](25) NOT NULL,
    [WEEK_START_DATE] [datetime] NOT NULL,
    [WEEK_END_DATE] [datetime] NOT NULL,
    [TIMESHEET_STATUS] [varchar](25) NOT NULL,
    [REMARKS] [varchar](250) NULL
)

CREATE TABLE [dbo].[TIMESHEET_DETAIL](
    [TIMESHEET_ID] [int] NOT NULL,
    [DATE] [datetime] NOT NULL,
    [PROJECT_ID] [varchar](25) NOT NULL,
    [TASK_ID] [varchar](25) NOT NULL,
    [TIME_IN] [datetime] NULL,
    [TIME_OUT] [datetime] NULL,
    [HOURS] [real] NOT NULL,
    [REMARKS] [varchar](250) NULL
)

Just a brief explanation on the Tables.

The TIMESHEET table sample entries are listed below :

TIMESHEET_ID  EMPLOYEE_ID  WEEK_START_DATE  WEEK_END_DATE  TIMESHEET_STATUS  REMARKS

1         EMP102       4/28/2013        5/4/2013        Open         NULL
2         EMP001       4/28/2013        5/4/2013        Open         NULL

The TIMESHEET_DETAIL table sample entries are listed below :

TIMESHEET_ID  DATE      PROJECT_ID  TASK_ID  START_TIME END_TIME  NUM_HOURS  REMARKS

2             5/2/2013  PRJ45        TASKA01    NULL    NULL       3.2        NULL
2             5/4/2013  PRJ45        TASKA01    NULL    NULL       2.2        NULL
2             5/4/2013  PRJ45        TASKB01    NULL    NULL       1.6        NULL
2             5/4/2013  PRJ45        TASKE01    NULL    NULL         1        NULL
1             5/3/2013  PRJ45        TASKA01    NULL    NULL       2.5        NULL
1             5/3/2013  PRJ45        TASKB01    NULL    NULL       0.5        NULL
1             5/4/2013  PRJ45        TASKA01    NULL    NULL       1.5        NULL
1             5/4/2013  PRJ45        TASKB01    NULL    NULL       0.5        NULL
1             5/4/2013  PRJ45        TASKC01    NULL    NULL       2.5        NULL
1             5/4/2013  PRJ45        TASKD01    NULL    NULL         3        NULL

The TIMESHEET table is the parent table and the TIMESHEET_DETAIL table is the child table. The TIMESHEET table will have one entry per week for each employee and the status will be Open, Pending for Approval, Approved, Rejected etc. The TIMESHEET_DETAIL will have details about each and every activity spent during that week and the number of hours spent on each of those activities.

My question is regarding the PRIMARY KEY for the TIMESHEET table and the TIMESHEET_DETAIL table. Right now there are 2 options for the PRIMARY KEY for the TIMESHEET table.

Option 1

Choose an auto-generated PRIMARY KEY. We have chosen this approach currently. TIMESHEET_ID is an auto-generated INT type PRIMARY KEY.

Option 2

We do not need the TIMESHEET_ID. The EMPLOYEE_ID together with the WEEK_START_DATE and WEEK_END_DATE can uniquely identify any row in the TIMESHEET table. But I feel it does not meet the criteria of being simple.

Which option is better for the TIMESHEET table and why ?

For the TIMESHEET_DETAIL table the composite PRIMARY KEY currently consists of ( TIMESHEET_ID, DATE, PROJECT_ID, TASK_ID ). If we do not have the auto-generated PRIMARY KEY in the TIMESHEET table then we will only be having ( DATE, PROJECT_ID, TASK_ID ) as the PRIMARY KEY for the TIMESHEET_DETAIL table.

Is there any better alternatives ?

I don't see how you are able to join the entries of `TIMESHEET_DETAIL` to entries of `TIMESHEET` without the `TIMESHEET_ID`. I'd think that is required here, but I may be wrong. Since `TIMESHEET_ID`, `WEEK_START_DATE` and `WEEK_END_DATE` are all in table 1, what criteria do you use to create a join? You'd have to use a combined index of the three mentioned columns... — arkascha, May 07 '13 at 07:21
There are a billion questions on Stack overflow already discussing this. http://stackoverflow.com/questions/63090/surrogate-vs-natural-business-keys http://stackoverflow.com/questions/590442/deciding-between-an-artificial-primary-key-and-a-natural-key-for-a-products-tabl http://stackoverflow.com/questions/6109182/composite-primary-keys-and-influence-on-natural-surrogate-keys-usage http://stackoverflow.com/questions/3747730/relational-database-design-question-surrogate-key-or-natural-key — Erik Funkenbusch, May 07 '13 at 07:28

score -2 · Answer 1 · answered May 07 '13 at 07:17

Option 2 is good if you want make academical right first normal form database, but in real life Option 1 is better.

In real life your application not ended in this two tables. You will need link database records with others application parts (tickets views, actions and etc.) in this case use on field primary key (it may be number or GUID or anther) is better.

Database Table Design - Primary Key

Option 1

Option 2

1 Answers1