What is a clean way to store items that reference multiple other items of the same type in a relational database?
Say I have a list of jar files and their dependencies:
package1 depends on package2,package3 package2 depends on package3 package2 depends on nothing
Now I could store that in a database as follows
CREATE TABLE packages ( pid STRING NOT NULL PRIMARY KEY, name STRING NOT NULL, dependencies INTEGER NOT NULL -- foreign key ) CREATE TABLE dependencies ( did STRING NOT NULL PRIMARY KEY, dependent INTEGER NOT NULL, -- foreign key dependentOn ??? )
But that would not be normalized and changing one relationship means touching all of them. How could I do that better?