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

share|improve this question
    
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. –  ypercube Mar 21 at 12:41

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

share|improve this answer

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.