I have a table where the rows can be related to each other, and logically, the relationship goes both ways (basically, is directionless) between the two rows. (And if you're wondering, yes, this really should be one table. It is two things of the exact same logical entity/type.) I can think of a couple ways to represent this:
- Store the relationship and its reverse
- Store the relationship one way, constrain the database from storing it the other way, and have two indexes with opposite orders for the FKs (one index being the PK index)
- Store the relationship one way with two indexes and allow the second to be inserted anyway (sounds kind of yucky, but hey, completeness)
- Create some kind of grouping table and have an FK to it on the original table to it. (Raises lots of question. Grouping table would only have a number; why even have the table? Make FK NULLable or have groups with single row associated?)
What are some major pros and cons of these ways, and of course, is there some way I haven't thought of?
Here's a SQLFiddle to play with: http://sqlfiddle.com/#!12/7ee1a/1/0. (Happens to be PostgreSQL since that's what I'm using, but I don't think this question is very specific to PostgreSQL.) It currently stores both the relationship and its reverse just as an example.
Best Answer
What you have designed is good. What needs to be added is a constraint to make the relationship directionless. So, you cannot have a
(1,5)
row without a(5,1)
row being added as well.This can be accomplished* with a self referencing constraint on the bridge table.
*: it can be accomplished in Postgres, Oracle, DB2 and all DBMS that have implemented foreign key constraints as the SQL standard describes (deferred, e.g. checked at the end of transaction.) Deferred checking is not really needed anyway, as in SQL-Server that checks them at the end of statement and this construction still works. You cannot do this in MySQL because "InnoDB checks UNIQUE and FOREIGN KEY constraints row-by-row".
So, in Postgres the following will match your requirements:
Tested at: SQL-Fiddle
If you try to add a row
(1,5)
:It fails with:
Additionally, you can add a
CHECK
constraint if you want to forbid(y,y)
rows:There are other ways to implement this as you mention, like storing only one direction of the relationship (in one row, not two) by forcing the lower id in
x_id1
and the higher id in thex_id2
column. It looks easier to implement, but usually leads to more complex queries later: