Best Table Structure

[Acropolis]

Hi,

Quick question for opinions.

I am looking at putting together a table to recording working patterns of employees, i.e. number of hours per day, and can't decide on the best structure to use:

Option 1

ID | UserID | Day 1 | Day 2 | Day 3 | Day 4 | Day 5 | Day 6 | Day 7

Option 2

ID | UserID | DayNumber (1-7) | Hrs

Normally I would go for something like Option 2 to allow for changes and additions in the future, don't like locking down to a fixed number of fields if the makes sense, but unless the world redesigns the gregorian calendar, then I can't see the number of days in a week changing in my life time (let's face facts who cares past that!)

Probably worth adding, this is only for a small number of employees (27 tops), and working patterns hardly ever change, and by that I don't know of any in the last 5 years, and they all work for me), so changing patterns isn't a major concern here.

Thoughts?

 

[pbaldy]

To quote a movie line, I'm conflicted. Normally I'd use option 2 as well, the properly normalized structure. I've used your option 1 in a couple of cases, for employee schedules. It works fine for some functionality. I still ended up with a union query to "normalize" the data so I could query against that for other functionality.

At the end of the day, you know best what your needs are, so if option 1 makes it easier to accomplish those needs, I say go for it.

 

[The_Doc_Man]

The question is never how to store it, but rather how to display it. Work from the display format backwards to get what you want.

(I agree with paul, though... I prefer to normalize when possible.)