0

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.

1
  • Not clear what you are up to. The linked_positions.position_ids seems not be needed as a column (can be calculated) and the linked_positions_1nf.id is not needed as well, the (link_id, position_id) could be used as the PK. Commented Mar 21, 2014 at 12:41

1 Answer 1

1

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 ;).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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