I am designing a base that contains product info, but the spec has just changed and I need a bit of help understanding the best structure. For simplicity let’s assume I have a table of products and a linked table of factories.
| ID | Make | Model | Factory (linked) |
|---|---|---|---|
| 100 | Box | Elite 2300 | Belfast |
| 101 | Faithful | Standard 20 | Dublin |
| 102 | Universal | 2020 Type 1 | London |
But now I’m being told that each product has multiple assembly’s, depending on a product type.
| ID | Make | Model | Type | Factory (linked) |
|---|---|---|---|---|
| 100 | Box | Elite 2300 | Hand tool | Belfast |
| 101 | Faithful | Standard 20 | Power tool | Dublin |
| 102 | Universal | 2020 Type 1 | Inspection tool | London |
| and each type will have multiple assemblies which may be manufactured in different factories. | |||
|---|---|---|---|
So do I need to have multiple rows in my product table?
| ID | Make | Model | Type | Assembly | Factory (linked) |
|---|---|---|---|---|---|
| 100-A | Box | Elite 2300 | Hand tool | Blade | Belfast |
| 101-B | Box | Elite 2300 | Hand tool | Plastics | Newtown |
| 101-C | Box | Elite 2300 | Hand tool | Packaging | Dublin |
or can I somehow structure the base to create a link between product and type and the assembly that then links to a particular factory?
Since we have to search for products I’m not keen to have multiple rows per product (as in the live base there are likely to be up to 12 assemblies per product).