How to normalize Time Sheet data?

Question

I am having a terrible time finding an appropriate way to store this data in a database.

The current excel (simple) version looks something like this:

http://i.stack.imgur.com/BEZOw.png

Staff and Client also have additional data associated with them, so it makes sense to use a foreign key relationship there. However, I am unsure how to store the hours data. Obviously you could make a column for each Time Period, however this seems like a very bloated approach.

I then thought that each "cell" that contains hours data can be represented like this:

http://i.stack.imgur.com/4VTXQ.png

However, this seems like the Staff and client key values are stored too many times. I have never designed a proper database before and would greatly appreciate suggestions.

*Sorry for not directly linking images, I do not have enough rep on this sub-site

Answer 1

You want to have something like this:

This allows you to have any number of work periods per staff member and client and gives you the details of who worked for who and how long (not to mention when - which is also very important!)

Answer 2

I would use a minimum of two tables to do this. The UserID will be the reference to your user tables.

CREATE TABLE TimeSheet(
    [ID] [int] IDENTITY(1,1) NOT NULL,  
    [UserID] [int] NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [EndDate] [datetime] NOT NULL)

CREATE TABLE TimeSheet_Detail(
    [ID] [int] IDENTITY(1,1) NOT NULL,  
    [TimeSheetID] [int] NOT NULL,
    [Hours] [float] NOT NULL)   

Answer 3

Something like this:

Employees { Staff } KEY { Staff } ;

Clients { Client } KEY { Client } ;

StaffAssignments { Client , Staff } 
   KEY { Client , Staff } 
   FOREIGN KEY { Client } REFERENCES Clients ,
   FOREIGN KEY { Staff } REFERENCES Employees ;

Timesheets { Client , Staff , Sequence , TimeGranule } 
   KEY { Client , Staff , Sequence } 
   FOREIGN KEY { Client , Staff } REFERENCES StaffAssignments ,
   CONSTRAINT Sequence >= 1 AND Sequence <= 55 , 
   CONSTRAINT TimeGranule > 0 ;