I was provided a database (with inadequate limited data already dumped in by a predecessor) and told to make it work. The end goal is to include all product lines and their component details at the time of shipment to a customer. Currently, the database includes one table with 1200 records. Each record contains multiple fields of data and appears to have originated from an Excel spreadsheet.
In most cases, each SHIPMENT would include multiple records. Each record would capture the details of each component shipped in a single system (product line). The product line includes multiple component part numbers that stay the same. Each component part needs to capture the following variable data: serial number, revision, software info, notes, etc. The customer and tracking info is duplicated for each line.
I have the following chunks of data I need to work with:
A customer may have multiple Purchase orders.
A purchase order may have multiple delivery addresses.
A purchase order may have one or multiple product lines.
A shipment may have one or more systems containing multiple serialized components, -and/or- individual components going to one address.
Each product line will have multiple serialized components and other info (revision, software version, etc.) to track.
Each component will have an independent serial number (and/or other info to track).
Each shipment will have a top level system serial number (and/or other info to track).
A product line (or component) can be shipped to multiple customers (or ordered from multiple purchase orders.
My initial thoughts were to have each product line as a separate table since the components stay the same and only a few fields for each component (serial number, revision, software version, notes, etc.) might vary from shipment to shipment. If I know how to format one product line (such as the 60cm dish in the provided sample) I am sure adding another product line will be fairly straight forward.
Other tables I was considering include one to capture the tracking info, ship date, contract shipped under, and destination. A Customer table is probably warranted (address, phone, contract/PO number, notes, etc.). Right now I am just trying to keep data capture going (record updates) as I figure all this out.
A copy of the database in question is attached.
This is my second database project. (The previous project was a few years back when in college. Thus this is a real struggle to pull this together. Any suggestions on table layout are very welcome.
Thank you in advance for your help.
In most cases, each SHIPMENT would include multiple records. Each record would capture the details of each component shipped in a single system (product line). The product line includes multiple component part numbers that stay the same. Each component part needs to capture the following variable data: serial number, revision, software info, notes, etc. The customer and tracking info is duplicated for each line.
I have the following chunks of data I need to work with:
A customer may have multiple Purchase orders.
A purchase order may have multiple delivery addresses.
A purchase order may have one or multiple product lines.
A shipment may have one or more systems containing multiple serialized components, -and/or- individual components going to one address.
Each product line will have multiple serialized components and other info (revision, software version, etc.) to track.
Each component will have an independent serial number (and/or other info to track).
Each shipment will have a top level system serial number (and/or other info to track).
A product line (or component) can be shipped to multiple customers (or ordered from multiple purchase orders.
My initial thoughts were to have each product line as a separate table since the components stay the same and only a few fields for each component (serial number, revision, software version, notes, etc.) might vary from shipment to shipment. If I know how to format one product line (such as the 60cm dish in the provided sample) I am sure adding another product line will be fairly straight forward.
Other tables I was considering include one to capture the tracking info, ship date, contract shipped under, and destination. A Customer table is probably warranted (address, phone, contract/PO number, notes, etc.). Right now I am just trying to keep data capture going (record updates) as I figure all this out.
A copy of the database in question is attached.
This is my second database project. (The previous project was a few years back when in college. Thus this is a real struggle to pull this together. Any suggestions on table layout are very welcome.
Thank you in advance for your help.