Question on best database design.

[BurtonBach]

I am relatively new to setting up databases. I am working on a database that will track some materials and trucking that our company uses. Currently I am creating a table called 'tblDayInfo'. It looks something like this:

DayID EmployeeRecNum Date DayStart DayEnd TotalHours
216 5 9/6/2006 7.00 18.00 3.5
217 13 9/6/2006 7.15 18.00 10.75
218 9 9/7/2006 8.00 14.00 etc
219 11 9/7/2006 etc etc etc

I just noticed that I have multiple DayID (the unique key for this table currently) for each date. I am concerned that this will cause me to be keeping the same data in multiple tables down the road.

Question: Should I create another column in this table that is a DateID type column that looks something like this:

DayID Date ID EmployeeRecNum Date DayStart DayEnd
216 1 5 9/6/2006 7.00 18.00 3.5
217 1 13 9/6/2006 7.15 18.00 10.75
218 2 9 9/7/2006 8.00 14.00 etc
219 2 11 9/7/2006 etc etc etc

I hope I am being clear enough. I simply want the best database design for this situation and I would appreciate any comment.

[NeoPa]

You need to consider this.
You have a table named tblDayInfo. This is a great name - except not for this table. It looks like a tblEmpDay type table to me. Don't have a field called DayID which doesn't identify a day! It is a recipe for disaster.
Naming is actually VERY important. Down the line when you've started work on other projects you won't remember, as you do now, that though it's called DayID it ACTUALLY refers to something slightly different. The name will be all you have.
So, You need a new name for this (presumably PK) field. You probably won't need an extra field for DayID as you have the date (only you know for sure).

[NeoPa]

Please visit this link (How to structure your tables). It doesn't discuss naming so much as database design. It should help you better understand the concepts. It's not that big a read and with this under your belt you WILL be a better Access programmer.