XMLType design implications

From: <dataplex_at_gmail.com>
Date: 1 Aug 2006 13:28:11 -0700
Message-ID: <1154464091.696471.213660@m79g2000cwm.googlegroups.com>

I'm designing a new database using Oracle 9i Release 2 (which we will later migrate to 10g). For the solution I chose to design an XML Schema for my data collection and store all my data as XML.

Using XML solves a couple of problems related to repeating fields (ex: Address lines, related web links, etc). XML has allowed me to have a vertically scalable solution. However, I'm confused on how I should integrate this design into the database.

Originally, with straight relational tables we were going to have two tables - one to hold top level items (Buildings) and another to hold Items (ie. departments, dining facilities, etc). My design migrated those two tables into one that looks like: ID Number PK,
ParentID Number FK ID Constraint,
ItemName varchar2(50),
ItemXML XMLType,
isActive Boolean

I was also planning to put these Items into categories using a Categories table (ID, ParentID, CategoryName, isActive) and link them using an ItemCategories link table (CatID, ItemID).

---

My question is this: Are there any guides to designing with XML? For example, the ItemName column could be removed becuase there is also a <Name> element in the <Item> Schema. I could theoretically use XQuery against that element to derive the Name instead of having a seperate column. The Items describe a variety of entities including buildings, departments, parking facilities, and other entities.

I'm later planning to be able to use XQuery to query specific parts of the XML Doc in the XMLType field (ex: GPS information and Name only), and use XSLT to transform an Item entry into an html page with detailed information.

Should I go with an ALL XML design or a hybrid XML/Relational model? The XML does not describe a transaction, request, or full document.

Any help would be appreciated, and you can contact me off list! Received on Tue Aug 01 2006 - 15:28:11 CDT