Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

3 Answers

up vote 5 down vote accepted

You want to have something like this:

ERD

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

share|improve this answer
    
The diagram looks cool. What tool did you use? –  Nanda Nov 1 '11 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 Nov 1 '11 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 Nov 2 '11 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? –  Kirk Broadhurst Nov 7 '11 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 Nov 8 '11 at 14:37
add comment

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)   
share|improve this answer
add comment

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 ;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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