By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,400 Members | 981 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,400 IT Pros & Developers. It's quick & easy.

Question on best database design.

P: 58
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.
Dec 4 '06 #1
Share this Question
Share on Google+
2 Replies


NeoPa
Expert Mod 15k+
P: 31,661
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).
Dec 4 '06 #2

NeoPa
Expert Mod 15k+
P: 31,661
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.
Dec 4 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.