Optimal database structure design for one to many

Question

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 part_id and the other with part_id, sub_part_id (which refers to another part_id) and quantity. so one table part_id 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, part_id,quantity;part_id,quanity;.... I would then use the PHP explode() function to separate by semi-colon and again by comma to get an array of the sub parts.

I hope this all makes sense. I am using PHP/MySQL.

*community wiki because this may be subjective.

Answer 1

Generally, option 1 is preferable to option 2, not least because some of the part IDs in the assembly would themselves be assemblies.

You do have to deal with recursive or tree-structured queries. That is not particularly easy in any dialect of SQL. Some systems have better support for them than others. Oracle has its CONNECT BY PRIOR system (weird, but it sort of works), and DB2 has recursive WITH clauses, and ...

Answer 2

NEVER, never ever use procedural languages like PHP or C# to process data structures when you have a database engine for that. Relational data structures are much more faster and flexible, and surer, than storing text. Forget about Option 2.

You could use recursive UDFs to retrieve the whole tree with no big fuss about it.

Answer 3

How about a nullable foreign key on the same table? Something like:

CREATE TABLE part (
    part_id int not null auto_increment primary key,
    parent_part_id int null,
    constraint fk_parent_part foreign key (parent_part_id) references part (part_id)
)

Answer 4

Definitely not option 2. That is a recipe for trouble. The correct answer depends on how many potential levels of assemblies are possible, and how you think of the assemblies. Do you think of an assembly (a composite onject consisting of 2 or more atomic parts) as a part in it's own right, that can itself be used as a subpart in anothe assmebly? Or are assemblies a fundementally differrent kind of thing froma an atomic part?

If the former is the case, then put all assemblies and parts in one table, with a PartID, and add a second table that just has the construction details for those parts that are composed of multiple other parts (which themseleves may be assemblies of yet more atomic parts). This second table would look like this:

   ConstructionDetails
  PartId,  SubPartId, QuantityRequired

If you think of things more like the second way, then only put the atomic parts in the first table, and put the assemblies in the second table

   Assemblies
   AssemblyId,  PartId,  QuantityRequired