Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

  1. Store the relationship and its reverse
  2. 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)
  3. Store the relationship one way with two indexes and allow the second to be inserted anyway (sounds kind of yucky, but hey, completeness)
  4. 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.

share|improve this question
    
Can a given value be related to more than one other? Is a given value always related to another? Do they share the same other common data? –  Phil Aug 23 '13 at 1:41
    
Yes, they can be related to more than 1 other row. No, they are not necessarily always related to another row. They don't necessarily have any common data. Thank you. –  jpmc26 Aug 23 '13 at 1:48
    
Oops. Forgot the @Phil. Also edited to add a potential structure that just occurred to me. –  jpmc26 Aug 23 '13 at 22:03
add comment

1 Answer 1

up vote 6 down vote accepted

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:

CREATE TABLE x
(
  x_id SERIAL NOT NULL PRIMARY KEY,
  data VARCHAR(10) NOT NULL
);

CREATE TABLE bridge_x
(
  x_id1 INTEGER NOT NULL REFERENCES x (x_id),
  x_id2 INTEGER NOT NULL REFERENCES x (x_id),
  PRIMARY KEY(x_id1, x_id2),
  CONSTRAINT x_x_directionless
    FOREIGN KEY (x_id2, x_id1)
    REFERENCES bridge_x (x_id1, x_id2)
);

Tested at: SQL-Fiddle

If you try to add a row (1,5):

INSERT INTO bridge_x VALUES
(1,5) ;

It fails with:

ERROR: insert or update on table "bridge_x" violates foreign key constraint "x_x_directionless"
Detail: Key (x_id2, x_id1)=(5, 1) is not present in table "bridge_x".:
INSERT INTO bridge_x VALUES (1,5)

Additionally, you can add a CHECK constraint if you want to forbid (y,y) rows:

ALTER TABLE bridge_x
  ADD CONSTRAINT x_x_self_referencing_items_not_allowed
    CHECK (x_id1 <> x_id2) ;

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 the x_id2 column. It looks easier to implement, but usually leads to more complex queries later:

CREATE TABLE bridge_x
(
  x_id1 INTEGER NOT NULL REFERENCES x (x_id),
  x_id2 INTEGER NOT NULL REFERENCES x (x_id),
  PRIMARY KEY(x_id1, x_id2),
  CONSTRAINT x_x_directionless
    CHECK (x_id1 <= x_id2)                       -- or "<" to forbid `(y,y)` rows
);
share|improve this answer
    
If you have a constraint that doesn't allow (1,5) without (5,1) and vise versa, how would you ever enter any data? You wouldn't be able to enter either row first and I don't know of any way to enter both at the same time. –  Kenneth Fisher Aug 24 '13 at 15:08
1  
@KennethFisher I don't understand your comment. Constraints are usually checked at the end of the transaction. Even a single Insert statement, as one transaction, can be used to insert 2 rows, like the provided SQL-Fiddle. You can check that it works. –  ypercube Aug 24 '13 at 18:19
1  
"I don't know of any way to enter both at the same time" With: INSERT INTO t VALUES (1,5), (5,1); ... –  ypercube Aug 24 '13 at 18:23
    
I didn't realize that constraints were checked until the end of the transaction :). Also is INSERT INTO t VALUES (1,5), (5,1) considered a single insert statement similar to INSERT INTO SELECT FROM or multiple INSERT INTO statements. –  Kenneth Fisher Aug 24 '13 at 19:29
    
@KennethFisher As I have been corrected, SQL-Server supports checking of constraints only at the end of the statements (not deferred to end of transactions) but still, the above approach works as an INSERT is indeed one statement, even if it inserts thousands of rows. –  ypercube Aug 24 '13 at 20:14
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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