Question on how to normalize data

[deanbri75]

I inherited a property control database that seems poorly set up. It's almost like each table was an a spreadsheet in a workbook.

The database has a table named for each year the inventory is performed, i.e. 2003, 2004, 2005. . . They all have the same fields and types:

Field Name Field Type
Dept# Text -- this is the primary key
DeptName Text
Delivered Date/Time
Returned Date/Time
ItemsMissing Number
NotFound Currency
%Found Number
10%Sample Number
SampleOrdered Yes/No
Total Currency
Comments Text
LetterSent Yes/No
Extension Yes/No
ExtDate Date/Time

What does change from year to year can be things like the addition or removal of Departments (and department number). I'm just not sure how to normalize this data. Departments are added and removed and the audits are done every year to each department. I just know there has to be a better way than copying the table structure and pasting it to create a table for the subsequent year.

Any suggestions on how I might reorganize this data would be greatly appreciated.

 

[lespaul]

First, read the document below.

Second, you will want to create a single table (based on the information you learn in the document) and combine all the data from prior years (your gut is right, it's a bad design!) into a single table (this can be done using queries, so you won't have to manually move everything!).

HTH

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design