ProdigyOne2k
asked on
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
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
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
http://www.scribd.com/doc/9137532/STAR-AND-SNOWFLAKE-SCHEMA
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This got me going in the right direction
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.