I am designing my first ever database architecture, no real framework to build up on. So I am working on my own! Terrifying.

A huge question I have is for having two separate tables, one being products, and one being components, and joining them to create a specific part list for a certain build. I'm still messing around with laying out a framework but I need a way for all of tables to communicate, while not having everything together in one place, to make the addition of new products and new components easier.

A thought I had for my architecture is having these two tables:

Products Table

product_id | product_name | part_list_id
abc123     | product_one  | listb
xyz789     | product_two  | lista

Components Table

part_id | part_name   | part_list_id
62345   | thumb screw | listb
10242   | ziptie      | lista

And to get each item would just be a simple

SELECT * FROM Components WHERE part_list_id = 'listb'

I wish it could be that simple. The thing that I know of that will make this awful is if a part is both in lista and listb. Like the addition of label below, which just adds unnecessary duplicates.

part_id | part_name   | part_list_id
62345   | thumb screw | listb
10242   | ziptie      | lista
14141   | label       | lista
14141   | label       | listb

I guess my question would be, is there a better way to have two different items share specific rows from a separate table, without sharing every other row aswell?


Your structure so far looks reasonable with a few caveats, so don't be too hard on yourself.

I wouldn't store any information about the components in the Products table directly if possible, so get rid of the part_list_id col. The product_id will give you the hook to build the component list later. This also means that you could add 'temporary' products to the table without needing to commit to a set of components as well.

For the Components table, I would name the cols component_id and component_name, just for consistency (and again, get rid of the part_list_id col as well).

Now, you will need a 3rd table to represent the components that make up a product. You could call this the "ProductComponents" table I guess, and it would have 3 columns at a minimum: productcomponent_id, product_id, and component_id. The productcomponent_id col should be the primary auto-generated key.

Now, if you want to get a listing of the components that are needed for a product, you just search the ProductComponents table by product_id and you can JOIN in the component_name if it helps:

select pc.*, c.component_name from ProductComponents pc 
join Components c on pc.component_id = c.component_id
where pc.roduct_id = 1
  • Thank you for the input! I appreciate it. I'm assuming since the products will be made on a manufacturing line there will be testing of each product and stored in my structure. Do you think I should go a similar route with adding a "Tests" Table and a "Tests_Results" Table, combining them like you do above? Just a thought – Travis Jan 15 at 13:46
  • 1
    Yep, the Tests table would describe the test, and the Test_Results would have a col for the Test id, a col for the Product id being tested, and any other data fields you need. – Graham Jan 15 at 13:50
  • Awesome, Thank you so much for the help. – Travis Jan 15 at 13:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.