Database Designing and Normalization issue

Question

Hi friends,

I have a huge access mdb file which contains a single table with 20-30 columns and over 50000 rows and i have some thing like this

columns:

id desc name phone email fax ab bc zxy sd country state zip .....
1  a     ab  12     fff   12  w 2  3   2    d     sd     233
2  d     ab  12     fff   12  s 2  3   1    d     sd     233

here I have some column values related to addresses repeating is there a way to normalize the above table so that we can remove duplicates or repeating data.

Thanks in advance.

Answer 1

Here's a quick answer. You just need to move your address fields to a new table (remove dups) and add a FK back to your primary table.

Table 1 (People or whatever)

id desc name phone email fax  ab  bc  zxy  sd   address_id 
1  a     ab  12     fff   12   w  2   3    2    1
2  d     ab  12     fff   12   s  2   3    1    2
3  d     ab  12     fff   12   s  2   3    1    2
4  d     ab  12     fff   12   s  2   3    1    1

Table 2 (Address)

address_id country state  zip .....
1          d       sd     233
2          e       ac     123

Answer 2

Jim W has a good start, but to normalize even further, make your redundant address elements into separate tables as well.

Create the tables for which address data is repeated (Country, State, etc.) Once you have your data tables, you'll want to add columns such as StateID, CountryID, etc. to the Address table.

You now have options for fixing the existing data. You can be quick and dirty and use Update statements to set all the newly created ID fields to point to the right data table.

UPDATE Addresses SET StateID=1 WHERE STATE='AL'

You can do this fairly quickly as a batch .sql file, but I'd recommend a more programmatic solution that rolls through the Address table and tries to match the current 'State' to an entry in the new States table. If found, the StateID on the Address table is updated with the id from the corresponding row in States.

You can then delete the old State field from the address table, as it is now normalized nice and neatly into a separate States table.

This process can be repeated for all redundant data elements. However, IMO db normalization can be taken too far. For example, if you have a commonly used query that, after normalization, requires 10 joins to accomplish, you may see a performance reduction. This doesn't appear to be the case here, as I think you're on the right track.

From a comment above: @Lance i wanted something similar to that but here is the problem i have raw data coming in the form of single table and i need to refine and send it to two tables i can add address in table 2 but i m not undertanding how would you insert the address_id in table 1

You can retrieve the newly created ID from the address table using @@IDENTITY, and update the address_ID with this value.