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:


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:


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

3 Answers 3


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!)

  • The diagram looks cool. What tool did you use?
    – Animesh D
    Commented Nov 1, 2011 at 16:38
  • 3
    I used Visio with ERD smart shapes that I built based on the James Martin ERD conventions and a custom line dash type that uses a wavy pattern. I use this to do conceptual or working ERD sketches. I find that the hand-drawn look helps to reinforce with some customers that it is a work in progress or sketch and not a carved in stone design.
    – Joel Brown
    Commented Nov 1, 2011 at 16:54
  • "This allows you to have any number of work periods per staff member and client" ...**and** date i.e. you haven't got a key that will prevent duplicates.
    – onedaywhen
    Commented Nov 2, 2011 at 11:57
  • 1
    @onedaywhen don't second-guess the requirements! Who says a staff member can't do two distinct lumps of work for a client on a given day? Commented Nov 7, 2011 at 21:26
  • @Kirk Broadhurst: if that were the case the hours for both would be added together; I think having two rows would look suspicious to an auditor, baring in mind that the enterprise has no knowledge of what WorkID means, being a fabrication. And you accuse me of second-guessing ;)
    – onedaywhen
    Commented Nov 8, 2011 at 14:37

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

    [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)   

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 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.