I am building an inventory tracking system for internal use at my company. I am working on the database structure and want to get some feedback on which design is better*.I need a recursive(i might be using this term wrong...) system where a part could be made up of zero or more parts. I though of two ways to do this but am not sure which one to use. I am not an expert in database design so maybe there is a their option that i haven't thought of.
- Option 1:Two tables one with the \[code\]part_id\[/code\] and the other with \[code\]part_id\[/code\], \[code\]sub_part_id\[/code\] (which refers to another \[code\]part_id\[/code\]) and \[code\]quantity\[/code\]. so one table \[code\]part_id\[/code\] would be unique and the other table there could be zero or more rows showing all the parts that make up a certain part.
- Option 2:One table with part_id and assembly. assembly would be a text field that looks something like this, \[code\]part_id,quantity;part_id,quanity;\[/code\].... I would then use the PHP \[code\]explode()\[/code\] function to separate by semi-colon and again by comma to get an array of the sub parts.