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?

Improve this question
1
  • Is the package2 depends on nothing line meant to be package3? Commented Feb 20, 2016 at 20:24

1 Answer 1

Reset to default
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.

Improve this answer
2
  • 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 Commented Feb 20, 2016 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. Commented Feb 20, 2016 at 20:56

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.