Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister

Re: Tree Structure and Db

by SimonClinch (Deacon)
on Jul 06, 2005 at 13:35 UTC ( #472793=note: print w/replies, xml ) Need Help??

in reply to Tree Structure and Db

1) The standard solution is to use a link table from the main table to itself. The link table contains two foreign keys from the same main table, i.e. the main table has TWO one-to-many relationships to the link table, one for parent relation and the other for child relation.

2) But to get performance out of this, it is best to write some access stored procedures (and in some cases perhaps views) which include a GetChild and a GetParent along with any procedures or triggers for insert, update and delete that may be required to simplify and unify access, but which otherwise hide (or rather make it unnecessary to expose) the link-table implementation to the database user ("user" includes any perl code that transacts with it).

One world, one people

Replies are listed 'Best First'.
Re^2: Tree Structure and Db
by simonm (Vicar) on Jul 06, 2005 at 16:26 UTC
    In a tree-wise data structure, with only one parent per child, what is the perceived advantage of using this linking table rather than just adding the parent ID to the main table?
      A link table is the normal way to enforce referential integrity in many to many relationships (0 or 1 counts as many for these purposes) and in this case prevents orphans; it also enables you to define different types of relationship without putting more illegal or awkwardly-implemented constraints (and adding maybe-null foreign keys for them) on the master table.

      One world, one people

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://472793]
[robby_dobby]: Corion: Oh, that's awesome! Your timing is perfect enough to see all hell break loose when you get back at work :P
[marto]: well, let me know in advance, I'll buy you a pint :)
[Corion]: robby_dobby: No, I'm returning in the second workweek of January. The main hectic parts are in the first days after the start of the new year
[robby_dobby]: marto: and, I'm not sure I'd be around here long enough for YAPC::EU
[marto]: Corion nice, what are you plans for Christmas?
[robby_dobby]: Corion: Nice, you just won't be around to deal with the mess - enjoy your vacation :-)
[robby_dobby]: marto: Sure, will do. Thanks!
[Corion]: marto: No great plans - I'll meet with my sister, my brother and my mother, but that's all :)
[Corion]: ... and also a visit to my godson+family of course :)

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (7)
As of 2017-12-15 10:41 GMT
Find Nodes?
    Voting Booth?
    What programming language do you hate the most?

    Results (431 votes). Check out past polls.