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