Database Normalization Help

I am trying to take what is a de-normalized database which has only 1 table and an unmanageable amount of columns with repeating info all over and turn it into something 3NF normalized - but I keep getting stuck.

I'm getting stuck with Serial Numbers, Description, and Manufacture columns which are attributes of PartNumbers

Some PartNumbers have Serial Numbers, some don't - how do I handle that?
Sometimes we have a Return Order and the Serial Number is returned into stock so we're using the same serial number again - how do I handle that?
I thought I would create a "SerialNumber" table - but I don't know how to tie everything together - and since SerialNumber may *not* be unique I don't know what the PK would be...a SerialNumber currently is first recorded on a PurchaseOrder to bring into inventory - but then can either be pulled "out" of inventory either if it's on a SalesOrder - or is a ReturnOrder back to the Manufacturer.

Some PartNumbers have descriptions that change - how do I handle a PartNumber with a description that changes?  And if that PartNumber is sold - how does it know which Description to use?

I understand Database Normalization from all the examples people post online - but then when I try to apply them to my own situation it seems like there are always way more attributes than the examples ever dealt with

Any help much appreciated


ProdigyOne2kAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
Can you break the data into groups?
A crewd way you can export the data as a CSV and then use it as in import into an Access database and then get Access to normalize it as an example.

It might be better/simpler to start from scratch. And then use a conversion process to extract data from the old one table, into the new normalized.
PK, fk.
0
APNFSSCCommented:
sounds to me like you might want to read up on snowflake schema... just at thought.

http://www.scribd.com/doc/9137532/STAR-AND-SNOWFLAKE-SCHEMA
0
Scott MadeiraCommented:
It sounds like your environment has some basic business process issues that you may not be able to fix (re-use of serial numbers, change in descriptions without a change or rev of the part number.)  

My personal opinion is that "real" databases in the "real world" sometimes need to deviate from 3NF to be manageable and functional.  3NF is a great academic idea but academia and reality often don't intersect.  If I were building your database given the info that you provide I would probably do something like:

Build a part number table.  My assumption is that a part number is a unique entity.  In that table have partnumberID, description and a manufacturerID column.

Manufacturers should be a separate table.

For actual instances of the items I would create a parts table where you would have an itemID, partnumberID, serialnumber (which can be null) for parts that don't have serial numbers, date purcahsed, date returned and any other columns that are specific to that particular part.

This allows you to change descriptions for a given part number and serial number can allow nulls.

Hope that helps.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ProdigyOne2kAuthor Commented:
This got me going in the right direction
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.