2

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?

  • Is the package2 depends on nothing line meant to be package3? – Iker Feb 20 '16 at 20:24
3

To answer the specific question you asked, the usual way to model a 1:N dependency on a normalized relational database is to put the link on the N side. That would mean, in this case, adding a sort of "is depended on by" column on the package table (name it however it makes sense to you), and not making a second table.

However, and moving beyond the specific question to the problem you're trying to solve, I'd argue that dependencies are generally M:N relationships. Even in your example, package3 appears twice in the dependency list1. Besides, you never know when you're going to make a package4 which also depends on package2, which would force you to rewrite the database to support that (suddenly) M:N link. Thus, I would personally model it by having another table like

CREATE TABLE dependencies (
    dependent INTEGER NOT NULL, -- foreign key
    dependentOn INTEGER NOT NULL -- foreign key 
)

I think having a dependency ID as in your example is a bit superfluous in this case.


1 Assuming you mean package1 depends on package3 by means of package2, that would indeed be a 1:N relationship, but in that case you wouldn't establish a direct link between package1 and package3 in the database.

  • 1
    Darn, I was writing a very similar answer but you went one step farther with it by dumping the dependency id entirely, which I should've thought of too. +1 – Ixrec Feb 20 '16 at 20:46
  • @Ixrec I saw it well explained in another answer earlier (not sure if here or at StackOverflow): 1:N relationships are best thought of as optimizations of M:N relationships. And unless you're really, really sure that there's no way the 1:N will turn into more, you run smack into the old adage about premature optimization. – Iker Feb 20 '16 at 20:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.