Hi,
I want to buil a hierarchical database, with different kind of relations.
I have differents elements which are linked between them by different kinds
of relations.
Ex:
element 1 IS A element 72
element 22 IS PART OF element 36
....
I want to have a table "elements",for several reasons: I want to keep each
element unique and indexed, if the definition of element 72 has to been
modified is relation to element 1 wouldn't be modified as I'm working with
indexes only to express the relationship and if element 72 has an other
relation with something else it is updated at once.
Then I think about having a table "relations", something like, "id_child",
"id_parent", "kind of relation".
that would give for example
1,72,IS A
22,36, PART OF
...
Until here I think this is the right way to proceed, because it's the more
flexible approach and will allow all the possible interactions.
For the final depending application, we want to output a graphical tree of
the relations/elements.
I think this is possible with that design with performances ok as we won't
have huge degrees of depth and we won't have a huge number of elements.
Now the problem:
One user want to delete element 72 for example.
2 options: it's impossible because element 72 as a child or we decide to
warn the user and delete the childs at the same time.
We haven't made the final decision yet, but the mechanism is still the
same:deleting an element should check for existing children in the table
relations.
I'm used to work with myIsam, I could easily do somthing like select * from
"relations" where id_parent=72, but I would like to know if it is possible
to implement that with innoDB?
I also would like to delete the element (or store it somewhere else) if it
is orphaned, eg if element 1 was only the child of 72 and 72 is deleted the
record in the relations table as to be deleted but the record in the
elements table should be flaged or moved in an "orphan" table.
In addition, if a user is working on element 72 we want to "lock" all the
children.
Sorry for the long explanation, I hope it is clear enough....
2 questions:
- do you see any problem with the design I've choosen?efficiency in building
the tree for example,problem to establish relations?
- do you think it is possible to use innodb in an efficient way for that
(constraints and cascaded delete and locks) and do you have any good pointer
on how to do it?or is it better to keep myIsam and manually do the checks?
Thank you very much for taking time to read this, hope you will have any
idea/comment!
melanie
_________________________________________________________________
Want to block unwanted pop-ups? Download the free MSN Toolbar now!
http://toolbar.msn.co.uk/
