HiveBrain v1.2.0
Get Started
← Back to all entries
snippetMinor

How to normalize Time Sheet data?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
normalizesheettimehowdata

Problem

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:

https://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:

https://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

Solution

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

Context

StackExchange Database Administrators Q#7471, answer score: 5

Revisions (0)

No revisions yet.