October 25, 2002 at 9:52 am
We're currently in the early design stages for a new content management system - so far the planning has occurred outside of the DB team's remit.
We have a requirement to store pieces of information, e.g. the structure of a web page. This single item (object?) would then have an unspecified number of child items (properties?). Those children could also have child objects and so on - through a potentially unlimited number of recursions.
The team that have worked on this so far (from a OO/XML background) seem fixed on the idea of an object-oriented database as a back end solution.
As the DBA (from a production background) I need to make some recommendations but know nothing about OO databases and I'm not even sure how I would manage an unspecified number of recursive parent-child relationships from a relational database perspective.
Has anyone got any experiences to share regarding this type of solution, or OO databases or tips for supporting this requirement in a relational database.
Any links to some reasonable simple articles to give me a heads-up on OO databases would be good too.
Many Thanks
. . Greg
Greg M Lucas
"Your mind is like a parachute, it has to be open to work" - Frank Zappa
October 25, 2002 at 10:27 am
In terms of experience using OO databases, I have none, so I can't tell you the positives or negatives of that.
In terms of storing a recursive parent/child relationship, that is relatively easy; you can do it with a single table.
Each row consists of a PRIMARY KEY, whatever columns of data you need, and a nullable FOREIGN KEY column(s) that references the PRIMARY KEY column(s) of the very same table. For example:
CREATE TABLE WebPageStructure (
ItemID INTEGER NOT NULL,
ItemContent VARCHAR(1000) NULL,
... [additional content columns] ...
ParentItemID INTEGER NULL,
CONSTRAINT pk_WebPageStructure
PRIMARY KEY (ItemID),
CONSTRAINT fk_ThisItemsParent
FOREIGN KEY (ParentItemID)
REFERENCES WebPageStructure (ItemID))
This allows you to establish a parent/child relationship, with each row having a foreign key reference to its immediate parent, and objects at the top of the hierarchy having the value "NULL" in their foreign key column. Given any object, you can find all its immediate children with a simple query.
Matthew Burr
October 25, 2002 at 2:45 pm
Haven't used OO dbs. Heard good things about them, but they appear to be a niche. Maybe because of complexity, maybe because IBM/Oracle/MS don't push them. Don't know. CA has Poet or Jasmine as an OO db. Might be worth trying and I'd be intersted to see what you think.
You can also use an "ad hoc" table to store children. We used to have a parent and then a child table with a "type" and a "value" to store ad hoc, sparse information.
Steve Jones
October 28, 2002 at 9:12 am
Matt,
Thanks for your help. I'd sort of thought that the self-referencing table was going to be the basis for my solution but given that we have an unspecified number of child recursions - the logic for this will have to go into a business object. I can see mutiple calls from come C++ component querying this table for child objects for each successive level of objects returned until it gets an empty result set. I guess that this might be the best solution at the end of the day but I was hoping to get some inspiration for a cleaner solution.
Steve,
I'll look into Poet and see if it's any use. I'm also planning to download a trial copy of Cache5 (www.intersystems.co.uk) which looks as if it might be at least OO based.
I'll keep this topic posted of any progress I make.
Many Thanks,
. . Greg
Greg M Lucas
"Your mind is like a parachute, it has to be open to work" - Frank Zappa
October 28, 2002 at 9:25 am
Yes, that can be the annoying thing about traversing such relationships. You might check out a book by Joe Celko entitled SQL for Smarties. In that book, as I recall, he has some suggestions for how to improve queries against tables like this by creating a sort of "helper" table that establishes certain information about the relationships in the table, such as the number of levels between any given parent element and any one of its children. Using such a table, you can determine with a single query, how many descendant elements a particular element has (not just immediate descendants, but all descendants), and you can determine what the descendant elements are, so that you can obtain them all with one query, thereby avoiding repeated queries against the table.
Incidentally, I just wanted to clarify, as I have access to my copy of SQL for Smarties now. The "helper" table I was talking about has to do with the "Transitive Closure Model," and it's in Chapter 28 of the book. It's a great book, and I highly recommend it as it has solutions to a lot of complex problems that pop up in database programming.
Matthew Burr
Edited by - mdburr on 10/30/2002 11:08:35 AM
November 21, 2002 at 7:02 am
Thanks Matt
Been away so only just seen your update. The Celko book is obviously one for my Christmas list!
. . Greg
Greg M Lucas
"Your mind is like a parachute, it has to be open to work" - Frank Zappa
December 4, 2002 at 4:00 am
SQL for Smarties is great (as well as Celko's other books), one of the most important books for your shelf. I'd have a look at the nested sets model he describes in his book, or a model called Enumerated Paths (not in that book, but I guess you could find info about it on the net), they let you do unlimited number of levels in a hierarchy. Note that I didn't say 'unlimited number of recursions', as it is not a recursive row-by-row-based model as the 'normal' iterative/recursive model, but instead both of them are set-based and therefore perform much better. An OO-database is definately not something I would go with, check out http://www.dbdebunk.com for Chris Date and Fabian Pascal's opinion of ODBMS as a 'data model'. There is no alternative for the relational model, imho.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
December 9, 2002 at 1:01 am
One solution is to use OR-mapping (Object relational mapping).
By having a datalayer that converts the object model to the relational model the programmers can use their objects without thinking on how they are stored
and you can use your knowledge about SQL Server.
It may require some work to understand and implement it but it will be worth it.
For more info see
http://www.objectarchitects.de/ObjectArchitects/orpatterns/index.htm
Fredrik Hellebro
December 9, 2002 at 8:44 am
IMO, OODB is a marketing buzzword used by salesmen to confuse management. Ya -- they have a niche place in the market but RDBMS has proven for 20+ years to be a good idea,flexible, easy to use and understand.
I think this may be a feature of all DBMSs later on, just like triggers, scripts, etc. I certainly would bet the farm on new technology for a mission critical app.
December 9, 2002 at 5:39 pm
First off I have thought about this quite a bit. The answer is for the sake of performance always (especially with SQL) look for a relation method. However, that said keep in mind even thou the DB is relational it is in fact Object Oriented anyway. Each time you create a table you are creating a related object to items in that DB or another DB and a table to it's rights is an object. I would thou keep an open mind and consider the OO option and compare any and all ways of doing exactly what you want with maximum performance in mind. If in fact the data potentially has an unlimited number of recursions then OO will be an adminstrative nightmare in time and the relational process offers the best overall storage and administrative umph so to speak.
As for Joe Celko tyr this link for an example of a nested set which has a lot of merit for exactly what you are talking about so that you have 1 relational table. http://www.hammerdata.com/Newsgrp/309.html
December 10, 2002 at 12:33 am
Guys,
Thank you for all your thoughts and input. It looks as if in the long term we'll be better off using the relational model. This is what I'd hoped for since I didn't really want to go through the OODBMS learning curve - especially when there is still so much to master in RDBMS.
Thanks again for your help.
. . Greg
Greg M Lucas
"Your mind is like a parachute, it has to be open to work" - Frank Zappa
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply