Hi,
Looking for some advice.
I have a number of tables and I want to create a generalised way to be able to link any row in any of the tables to any other row in another table (or the same table).
So one approach I have considered is to have a new table to keep track of these links. Its structure could look like
--------------------------------------------
|id | Table 1 | Table 1 | Table 2 | Table 2| Comment
| | Name | Row ID | Name | Row ID |
--------------------------------------------
| 1 | A | 1 | C | 3 | Links Row 1 in table A
to Row 3 in table C
--------------------------------------------
| 2 | A | 1 | E | 1 |
--------------------------------------------
| 3 | B | 2 | D | 2 |
I sketched this and scanned it here:
https://docs.google.com/file/d/0B4ydfm9DZtAST2VJOHhRbkZkUDQ/edit?usp=sharing
Note that in reality connecting Table A row 1 to table C row 3 is the same as connecting table C row 3 to table A row 1.
This means that before I was to strore A1 - C3 I need to check both that A1-C3 is not already there and also that C3-A1 is not in the table either.
A similar problem exists when searhing the table. If I want to find everything that A1 is linked to I have to search both halves of the table.
Select * FROM xlinktable
where table1name = A and Table1RowID = 1
or table2name = A and Table2RowID = 1
Now this may be the best way to do this sort of thing.
But I figured before speding too much time implementing it, I would ask you gurus if this is the best or appropriate architecture.
Thoughts & comments requested.
Thanks