Hi,
I trying to design a table schema that will allow link relationships between
objects with the following features:
* Show a direct link relationship between two objects
* Be able to remove relationships
* Find indirect links between objects (This is were it gets tricky)
I have persisted with trying a number of table schemas over the last few
weeks to what initially seems a simple problem. However, I having difficulty
'nailing it on the head'.
Assuming each object is represented by a letter. How would you design a
schema for the above features?
A --> B --> C
\
\--> D --> E
In this example, A and B have a direct relationship whereas A has an
indirect relationship with C, D, E.
D has a direct relationship with B and E whereas it is indirectly related to
A and C.
I can think of two ways to implement the indirect relationships. The first
way was storing only direct relationships in the database and used the
application to recursively search for indirect links. (ie A is linked to B.
B is linked to C and D. C is linked to ... and so on). This would be slow
and a question is raised on how many times you would do a recursive search.
The second way is to store the indirect links as well (ie A to C, A to D, A
to E). This will grow the number of rows in the db at an increasing rate -
I've modelled it to be 0.5 * N * (N + 1) where N is the number of links (4
in the above). I don't expect the size to be an issue but I can not figure a
good way to remove indirect links from the db.
For example, How would you delete the indirect relationship A to E if the
direct link B to D was removed? What if (not shown) E also had a direct
relationship with A. The removal of the B --> D link would still leave
indirect links with E and C.
I appreciate this is not related to SQLite (though it is the db I'm using),
but I welcome any ideas to help me solve this 'simple' problem.
Many Thanks
Nick
********************************************************************
This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender.
You should not copy it or use it for any purpose nor disclose or
distribute its contents to any other person.
********************************************************************
