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.
Then, instead of storing property description text separately in
your categories and categoryLinks tables, have a foreign key
reference the id column in the property table.
In addition, I'd get rid of your categoryLinks table altogether.
Think like an object oriented programmer for a second. The
individual entries in the main Categories table and the individual
items tables are 'definitions' of that particular 'object' with some
'default' values filled in. The additional (seemingly redundant)
entries on the link table allow the link to 'override' the default
value(s) with local, instance specific ones. My goal was as a
time/work saving measure (from the administration perspective) to
allow 'children' elements to have blank entries where the values
would get inherited down from parents.
That makes sense. I haven't delved into the object-relational
properties of databases, but I wonder if that kind of inheritance is
easier to achieve than it seems?
Besides, my keywords are working nicely, I was just curious about SQL
and if there was any conditional recursive looping in the various
scripting syntax. (what is the plural of syntax? synti?)
If the object-relational thing works out, you may not have to worry
about recursion. I'd suggest investigating that route.
If all else fails and you still want to keep these operations inside the
database, stored procedures are great for that sort of thing. I write
utility and analysis functions in PostgreSQL's PL/Perl all the time, and
I expect it would be much easier to traverse a hierarchy if you were
able to do so in a language like that instead of in SQL.
As for the plural of "syntax", I don't think there is one, but it's
likely possible to pluralize etymologically ancestral versions of it. :)
Colin
