0

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!

srthompers
  • 169
  • 8
  • If the choice of numbers is predefined and will not change, you can have a look at MySQL's `SET` end `ENUM` data types. However, it's generally discouraged using them for various reasons. So if you want to make it completely "right", you'll be better off using reference tables. – Constantin Groß Feb 14 '17 at 15:20
  • 1
    Possible duplicate of [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – Shadow Feb 14 '17 at 17:38

1 Answers1

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 enter image description here

asugrue15
  • 65
  • 1
  • 8
  • If I understand correctly, I'd then have one record in my part_dependency table for every dependency? – srthompers Feb 14 '17 at 16:12
  • Yes but you would be able to create multiple Part_Dependency records where the Part_id is the same but each time the Dependent_part_id would be different. So taking your original example where Part IV depends on Parts I and III would require 2 Part_Dependency records: Part_Dependency_id = 1, Part_id = IV, Dependent_part_id = I; Part_Dependency_id = 2, Part_id = IV, Dependent_part_id = III. You could then query this with something along the lines of Select Dependent_part_id from Part_Dependency where Part_id = IV to give you a result of I and III – asugrue15 Feb 14 '17 at 18:56
  • You could then also query to find out all the parts that a given part is a dependent. For instance, you could see all the parts where part III is a dependent by querying something like Select Part_id from Part_Dependency where Dependent_part_id = III. Does this make sense? – asugrue15 Feb 14 '17 at 19:04