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

How to normalize a table with duplicated data and empty fields

P: 3
I have inherited a big, messy table with duplicated data and empty fields consisting of Names (business, client, supplier, etc.), Addresses ( physical, mailing, shipping, billing), Orders ( Date, item, quantity, price, shippedby, shipdate, shipcost), etc..

I wish to "normalize" this without loosing data.

MainTable.ID -> NamesTable (NameID, NameType, LName, FName, etc....)
-> AddressTable (AddressID, Type, Line1, Line2, Suite/Box, ....)
-> OdrersTable (OrderID, Item, Qty, Price, ShipFactor, ....)
-> more

Please direct me to a step - by step work flow of how to dissect a messy table into normalized tables without loosing data of its correct relationships.

Thank you!
Mar 16 '10 #1
Share this Question
Share on Google+
3 Replies


Expert Mod 100+
P: 2,328
First here's a primer to database normalization
http://bytes.com/topic/access/insigh...ble-structures

Second, you'll have to provide db structures so members can help you out.
Mar 16 '10 #2

P: 3
Thank you for your reply.

I have read Mary's post and believe that I understand the concept.

My Goal is:
Table t_Addresses
AddressID AutoNumber
MasterID long, default Null lookup to MasterTable.MasterID
StreetNbr
Mar 16 '10 #3

P: 3
Thank you for your reply.

I have read Mary's post and believe that I understand the concept.

My Goal is to make Table t_Addresses (test table has 610 records) w fields:
AddressID AutoNumber
MasterID long, default Null look-up to t_MasterTable.MasterID
StreetNbr text
StDirectionID long, default Null look-up to t_StDirections.StDirectionID
StreetName text
StreetTypeID long, default Null look-up to t_StreetTypes.StreetTypeID
SuiteID long, default Null look-up to t_Suites.SuiteID
CityID long, default Null look-up to t_Cities.CityID
(more)

And Tables, such as t_StDirections (there are about 19 unique directions)

StDirectionID long, look-up from t_Addresses.StDirectionID
StDirection text (ie. N NW W SW S Oeste Sur ....)

And Tables, such as t_StTypes (there are about 35 unique types)

StreetTypeID long, look-up from t_Addresses.StreetTypeID
StreetType text (Ave, Rd, Ln, Hwy, US 1, etc.)

My Starting Point is Table t_AddressesFromQuery (614 records some records do not have addresses) with fields:

ID AutoNumber, PK
AddressType text (ie. business, home, ship to, POBox, billing, ..)
StreetNbr text
StDirection text (ie. N S E W NW ...)
StreetName text
StreetType text (ie. Rd., St., Ave, Hwy. ...)
Suite text (ie. 101-A, Suite C, 3rd Floor, ...)
AddAdditional text (ie. rear, Bld.#3, ....)
City text (ie. Portland, Los Angles, ...)
State text (ie. CA, OR, OT ....)
ZipCode text
ZipExtension text
Country text (ie. US, CA, PR, PUR, ...)

I'd like suggestions on how to break out t_Addresses.StDirection into t_StDirections.

Thanks.
Mar 16 '10 #4

Post your reply

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