On 2008-04-15 18:55:25 -0400, Colin Wetherbee wrote: > Scott Webster Wood wrote: > >>You shouldn't really need to go recursing through a data tree to > >>come up with all the parts of a single fact. > >Well that is unless you are wanting to inherit data from linked > >elements further up a link list without re-posting redundant > >information. > > That's true enough in your situation, but then you trade speed against data > size. I suspect traversing a data hierarchy inside a database, with SQL > alone, > won't be too pleasant when it comes to performance.
Don't forget data consistency.
In the OP's design it is clear that NULL means "inherit from parent".
So consider these two cases:
Table categories:
Id Parent-Id Property
1 NULL foo
2 1 NULL
Table categories:
Id Parent-Id Property
1 NULL foo
2 1 foo
In the first case the child (id 2) inherits property foo from the
parent. If you change it in the parent, it will change in the child,
too. In the second case both have the property foo, but they are set on
both explicitely. If you change it in the parent, it will remain the
same in the child.
Now change this to a design with an explicit property table which linked
from all levels:
Table categories:
Id Parent-Id Property
1 NULL prop_1
2 1 prop_1
Table properties:
Id text
prop_1 foo
Now we have no distinction between "inherit from parent" and
"explicitely set to some value which just happens to be the same as that
of the parent".
We can use distinctive entries in the properties table:
Table categories:
Id Parent-Id Property
1 NULL prop_1
2 1 prop_2
Table properties:
Id text
prop_1 foo
prop_2 foo
but that's not quite the same: prop_1 and prop2 can be referenced from
anywhere in the categories tree and the user needs to be able to
distinguish them somehow. So let's drop that for the moment and assume
that properties.text is unique.
Then, when you want to change property on the parent row, you have three
choices:
1) Just change the text in the properties table. This will change it
everywhere in the database, not just in the row and its children.
2) Create a new property entry with the new text, and just change the
foreign key in the parent row to point to this new entry.
3) Create a new property entry with the new text, and recursively change
it in the parent and all descendants.
All of these are different from the orignal design. It's possible that
one of them matches the intentions of OP better than the solution he had
in mind, but you need to be aware of the differences.
There is of course a fourth method:
4) In addition to the foeign key to the properties table, add a flag
which tells whether the value was inherited or set explicitely.
On update, recurse as in 3), but change only values with the flag
set.
This restores the semantics of the OP's design.
One other point to consider: In the OP's design, every query for data
may need to go up to the root of the tree to find the value. So that may
be up to depth(tree) additional rows to read *for every query*. In the
cases of 3) and 4) you need to recurse down through the tree for every
update. This may mean up to nodes(tree) additional rows to update for
every update. So you are trading a small improvement in query time
against a potentially huge degradation of update time. So to determine
which is better you need to consider the frequency of queries and
updates, the size and shape of the tree, how many nodes have the
properties explicitely set vs. inherit them, etc.
And finally, this is the perl DBI mailinglist, not an SQL mailinglist.
There is nothing wrong with solving the problem in Perl, you don't have
to do it in SQL ;-).
So, you can for example do individual queries and cache the results (in
the object, if you are creating objects, or in a hash). The upper levels
in the catalog will almost always be in the cache, so you rarely need to
send a query for the parent.
hp
--
_ | Peter J. Holzer | If I wanted to be "academically correct",
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | [EMAIL PROTECTED] | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users
pgp6ILrihqgzD.pgp
Description: PGP signature
