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.