1

In my scheduling database, I have an "assignments" table with the following fields:

| assignment_date | start_time | end_time | task_ID | staff_ID |

Each staff member will have six or seven separate assignments per day, meaning that with the current staff numbers there will be approxmiately 250 individual entries for any individual date.

I was wondering if there are any situations where it would be worth extracting the date field into a separate table ( i.e. changing assignment_date to assignment_date_ID ).

Is this taking normalisation a step too far? I can imagine that it would make queries a fair bit more complex, but can you think of any advantages to this structure that would offset the extra complexity?

Alex
  • 1,633
  • 12
  • 12

1 Answers1

6

Replacing dates (or text, or anything else, for that matter) with an ID number has absolutely nothing to do with normalization. Hunt down whoever taught you that idea, and poke them in the eye with a sharp stick.

If you want to restrict the range of acceptable dates, you can use a foreign key to a table of acceptable dates. That's a good reason to add a table; it, too, has absolutely nothing to do with normalization (it has to do with data integrity); and you shouldn't put an id number in that table, either.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • I think I need to drastically improve my mental model of normalization. Do you have any references immediately to hand that could help enlighten me about IDs/Foreign Keys/normalization? Or should I just go googling :) – Alex Feb 28 '12 at 01:53
  • To start, Google `site:stackoverflow.com "nothing to do with normalization"`. – Mike Sherrill 'Cat Recall' Feb 28 '12 at 02:05
  • I think this is what I'm after http://stackoverflow.com/questions/6433644/data-normalization-and-writing-queries ... thanks for your help. BTW, I can see you weren't kidding about your favourite comments in your profile! – Alex Feb 28 '12 at 02:39
  • And a link to the actual relevant answer this time... http://stackoverflow.com/a/6474657/211869 – Alex Feb 28 '12 at 22:36