I am looking for a better way to create a link between two or more rows of the same table, but the link itself must have a unique ID. What I am currently doing seems rather convoluted. I have a positions table that I am "linking" via the table linked_positions, but using yet a tertiary link via the linked_positions_1nf table. Perhaps it will be easier to see the structures with some sample data…
mysql> SELECT id FROM positions WHERE id IN (104, 105);
+-----+
| id |
+-----+
| 104 |
| 105 |
+-----+
mysql> SELECT id, linked_ids position_ids FROM linked_positions LIMIT 1;
+----+--------------+
| id | position_ids |
+----+--------------+
| 1 | 104, 105 |
+----+--------------+
mysql> SELECT * FROM linked_positions_1nf WHERE link_id = 1;
+-----+---------+-------------+
| id | link_id | position_id |
+-----+---------+-------------+
| 123 | 1 | 104 |
| 124 | 1 | 105 |
+-----+---------+-------------+
It seems like it would be more ideal to only have one linking table to achieve the above, but I haven't been able to find a more refined solution. Thanks in advance for any guidance.
Best Answer
If the ID in your 'link' table absolutely has to be unique, and if it's ALWAYS a link between two IDs, you could use 3 columns: ID, min_position, max_position. That would allow you to use joins with your positions table.
I'm a bit confused regarding the goal of your application, i must admit ;).