Hello everyone,
I have a system (simplified for this example) with the following two tables:
TABLE1
id::int8
containerId::int8
containerType::varchar(100)
moreInfo::text
TABLE2
id::int8
containerId::int8
containerType::varchar(100)
otherInfo::text
Now, the rows of TABLE2 are children of TABLE1 objects. Therefore, an entry
in TABLE1 and TABLE2 looks like that:
TABLE1:
id containerId containerTypemoreInfo
-
1 0 null 'foo'
TABLE2:
id containerId containerTypeotherInfo
-
1 1 TABLE1 'bar'
2 1 TABLE1 'more bar'
3 2 TABLE2 're: more bar'
Since in this case TABLE1's row 1 is a root object it has no containerType
and containerId (it has no parent). The first 2 TABLE2 rows are sub-objects
of TABLE1, therefore TABLE1 is referenced there. But Tables (objects) can
also reference themselves, like it would be the case in a message board.
Now my problem:
-
I need to figure out the object-tree for any row in the system (of course I
do not only have 2 levels like in this example). The preferred output
should look like this (or similar):
level containerType containerId
--
1 TABLE1 1
2 TABLE2 2
3 TABLE2 3
This would be the object tree for row 3 in TABLE2. I am pretty sure one
would need to use pg/PLSQL or something similar, if that is possible at
all. I could solve the problem in my programming language with a recursive
function, but it would be VERY expensive, since I would need to make n DB
requests (n --> number of levels).
If you know a better way to represent a system like that generically, let
me know. I thought about making a base table with the fields
(containerType, containerId) and then inherit that table, but that did not
get me much further.
THANKS A LOT FOR YOUR TIME IN ADVANCE!!!
Regards,
Stephan
--
Stephan Richter
CBU - Physics and Chemistry Student
Web2k - Web Design/Development & Technical Project Management
