Hi all,
I mostly just lurk on the list, but I need to vent. And I could use any
advice/ experiences you'd be willing to share.
For those of you that don't want to read my rant:
What solutions are there to storing a wide/shallow self referencing tree in a
database?
Any pointers to previous discussions, articles or other background information
is very much apreciated. I seem to be googling in circles, the same articles
keep popping up.
If I learned one thing today it's that I need to educate myself a bit before
settling on an approach for this ;-)
Cheers,
Rob
Pre-P.S. If anyone is interested in my plpgsql version of the approach from
the dbazine.com article from my rant, you're welcome to it. I'd be happy to
post it to the list or sending it in the mail.
<RANT>
Today I was confronted with the problem of storing self referencing data, (The
popular tutorial material seems to be employees with a boss/subordinate
relationship.) I'm sure many of you have been there.
So like a good boy I went to trawl the pgsql archives and found some
references to the Celko 'nested set' model
[http://www.intelligententerprise.com/001020/celko.shtml]. After some more
googling around I found http://www.dbazine.com/tropashko4.shtml.
I'm not sure if any of you are familiar with this approach, but it's similar
to the 'nested sets' approach and somehow this approach appealed to me more
than the Celko 'nested sets'. ( And the 'Real World Performance group at
Oracle Corp.' sounds like a ringing endorsement )
I don't have any mathematical background, no formal CS education of note and
not a lot of experience with plpgsql programming. But... I'm a sucker for a
challenge.
So, equipped with my limited skillset, I have spent today wrestling with my
lack of skill, my unfamiliarity with the problem domain, inaccuracies in the
article, oracle to postgres translation quirks.
But I pressed on. Never loosing sight of my goal: the reward and pride of
overcoming this academic challenge.
It's been a long day, I managed to squeeze in a lunch break, two bathroom
visits and a few trips to the coffee machine. But my heart was light at the
prospect of a job well done.
And now,... at the end of the day I am proud to announce to the world:
"I have actually done it! Today I have acheived something I have not done
before. I am a better man tonight than I was this morning."
But,... it's a bittersweet victory because the provided solutions is
completely useless to me. It appears that when adding more than 48 sub nodes
to any node in the tree, craps out because of an INT8 column overflowing.
So after this aticlimax I've set aside my pride and turn to the list for
guidance.
</RANT>
