Storing a list of values in a mySQL record

Question

I'm very new to mySQL, so apologies if this is a stupid question. I'm currently working on a web-based e-learning tool. Rather than a linear progression from Part I to Part II etc, instead the sequence of parts is governed by interdependency, eg Part II may only depend upon Part I, while Part IV may depend upon Parts I and III, and so forth.

Currently, I have one table holding some higher-level references, and a second table containing all the information for all the parts, one record per part (each part contains data in a very homogenised and well-defined form, so this is appropriate). I was hopeful that I could have a single field capable of holding a list of numbers of arbitrary length, allowing any given part to depend upon an arbitrary number of other parts. Is there any easy way of achieving this, or am I best just storing this as a comma-delimited list in a string, and parsing it in php?

Many thanks for any pointers in advance!

Answer 1

I think you could achieve this by having an associative entity (e.g. part_dependency) that would need to have one foreign key for part_id and another for dependent_part_id).

Diagrammatically your entity diagram would contain a part table and the associative entity; the part table would relate to the associative entity twice (once for the first foreign key, and then again for the second foreign key).

UPDATE: Added Diagram