Hello.
I think such kind of logic could be implemented on database layer, because the main task for you is to keep data integrity, and this was one of the reason of database creation. However, without triggers your task becomes difficult. Unfortunately they're available only in MySQL 5.xx, which is not production ready yet. "mel list_php" <[EMAIL PROTECTED]> wrote: > 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/ > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
