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:
product_id | product_name | part_list_id abc123 | product_one | listb xyz789 | product_two | lista
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
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?