RE: Data Modeling Question

Regarding your options 2 & 3  

Have a look here to see if it feeds your needs: http://forums.oracle.com/forums/thread.jspa?messageID=2051275&#2051275  

1. Nethertheless, it won't solve you modeling problem, regardless if it is based on relational or xml type data.
2. CLOB will not perform. Not as CLOB - not as XMLType CLOB based storage. In Oracle 11g it might, if used in conjunction with XMLIndex
3. To this to be performant you must use XMLType based on object relational storage.
4. Object Relational XMLType storage is also the only performant method regarding modifications.
5. If XMLDB experience is not available, you will encounter a steep learning curve...

Marco

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jared Still Sent: vrijdag 8 februari 2008 22:12
To: ahbaid_at_att.net
Cc: oracle-l
Subject: Re: Data Modeling Question

It is still just a modeling problem.

Data is data.

When one feels a need to have a new column for each change in values of an attribute, it is more than likely that would benefit from sitting down with competent modeler and determining what that model should look like.

Following that someone would need to translate that model into a database design.

On Feb 8, 2008 11:37 AM, Ahbaid Gaffoor <ahbaid_at_att.net> wrote:

        I wanted to add a bit more info based on the warnings of the dreaded

        "attribute entity" approach.         

        Our database serves as a service, the actual name and value of the

        attributes is published by our clients to us. We receive several hundred

        attribute changes a day, so using a dedicated column name for each

        attribute would not work for us. We are facing the problem too that we

	will exceed 1000 columns soon. The attribute name/value data is
	meaningless to us, so we do not have any data integrity checks
against
	the data. Eventually the data will be encrypted so it becomes
completely
	meaningless to our service internally.
	
	The core problem we face is running out of columns and being
able to
	support 5000+ attributes, some used some not, which is why the
three
	approaches are being considered. The points about getting all
data for
	an item being more complex than a single row select are valid,
however
	we need a scalable service and all columns in one row just

        thanks again for the feedback         

        regards         

        Ahbaid         

	Ahbaid Gaffoor wrote:


	> We have a data model which looks like the following:

	>

	> ITEMS

	> ==========

	> ITEM_CODE              VARCHAR2(100)     (Primary Key col 1)

	> ITEM_TYPE               VARCHAR2(100)

	> ATTRIBUTE_001       VARCHAR2(4000)

	> ATTRIBUTE_002       VARCHAR2(4000)

	> ...

	> ATTRIBUTE_250       VARCHAR2(4000)

	>

	>

	> ITEM_TYPE_LU

	> ============

	> ITEM_TYPE               VARCHAR2(100)  (Primary Key col 1)

	> ATTRIBUTE_NAME  VARCHAR2(1000) (Primary Key col 2)

ATTRIBUTE_COLNUM


	> NUMBER

	>

	> It's a developer's dream of how we can store attributes for an

item,


	> the item type is used to decide which column holds what

attributes by


	> storing that metadata in the ITEM_TYPE_LU table. Initially,

some 7


	> years ago the developer only dreamed of no more than 10

attributes per


	> item. Today there are 205 attributes, some fields hold data

that's a


	> few characters and some the full 4000 characters.

	>

	>

	> I informed the development team that we face a max number of

columns


	> (1000 when I tested on 10.2.0.3)

	>

	> Now we are undertaking a redesign to provide a data model that

allows


	> us to store an unlimited number of attributes.

	>

	> The ideas we have in mind so far are:

	>

	> 1) Switch to a row based schema, so the two tables become one,

each


	> attribute and its value taking a row for an item.

	>

	> ITEMS

	> =====

	> ITEM_CODE                VARCHAR2(100)     (Primary Key col 1)

	> ATTRIBUTE_NAME     VARCHAR2(250)

	> ATTRIBUTE_VALUE    VARCHAR2(4000)

	>

	> So a single item would look as follows:

	>

	> SELECT * from ITEMS where ITEM_CODE = 'shoe-001';

	> ITEM_CODE  ATTRIBUTE_NAME ATTRIBUTE_VALUE

	> ---------------------------------

	> shoe-001   size   10

	> shoe-001   color   brown

	> shoe-001   price   15.45

	> shoe-001   manufacturer   clarks

	>

	> 2) Use a CLOB column and store an XML document in it with all

	> information:

	>

	> ITEMS

	> ======

	> ITEM_CODE                VARCHAR2(100)     (Primary Key col 1)

	> ITEM_XML                  CLOB

	>

	> SELECT * from ITEMS where ITEM_CODE = 'shoe-001';

	> ---------------------------------

	> ITEM_CODE  ITEM_XML

	> ---------- --------------------------------------------------

	> shoe-001   <item>

	>           <item_code>shoe-001</item_code>

	>           <size>10</size>

	>           <color>brown</brown>

	>           <price>15.45</price>

	>           <manufacturer>clarks</manufacturer>

	>           </item>

	>

	>

	> 3) Use Oracle's XML data types (not sure how this works)

	>

	> I am partial to the first approach since I would be able to

easily


	> parse data when a business request needs it, also I can make

use of


	> Oracle partitioning to improve performance.

	>

	> One other point to con sider is that single attributes can be

updated


	> for an item, in the row based model this is simple, however in

the


	> CLOB based solution I am concerned that we need to replace the

entire


	> CLOB  for a single attribute value change.

	>

	> What I'd like is any advice pros / cons of the CLOB (approach

2) and


	> the XML type (approach 3) solutions.

	>

	> For scale, we are looking at close to 200 million items, each

item


	> having about 150 attributes each.

	>

	> I am proceeding with approach 1 for now.

	>

	> many thanks for your input

	>

	> Ahbaid

	>

	>

	>

	>

	>

	>

	> --

	> http://www.freelists.org/webpage/oracle-l

	>

	>

	>

	
	--
	http://www.freelists.org/webpage/oracle-l
	
	
	

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist


--
http://www.freelists.org/webpage/oracle-l