RE: Data Modeling Question

From: Hostetter, Jay M <JHostetter_at_decommunications.com>
Date: Thu, 7 Feb 2008 10:49:08 -0500
Message-ID: <D67EB7CEECD4334F9C85759227553BBC0B4B4775@CL-EXCHANGE1.dande.com>

I agree with Mark.

We have a developer that recently setup a table this way. Now he want's to return all the attributes for an item in one row. So we've had to create a view that outerjoins on the table once for each attribute that he wants returned.

Select a1.item_code,a1.attribute,a2.attribute,a3.attribute  From items a1,

      items a2,
      items a3

 where a1.item_code=a2.item_code(+)
   and 'SIZE'=a2.type(+)
   and a1.item_code=a3.item_code(+)
   and 'COLOR'=a3.type(+)
Etc.

Embedding all of your attributes into one column will make it nearly impossible to query.

Jay

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Powell, Mark D
Sent: Thursday, February 07, 2008 9:23 AM To: oracle-l
Subject: RE: Data Modeling Question  

Unless your application normally only needs to retrieve a single attribute for an item_code per transaction just add a second attribute table to you application to handle attributes 1001+

Doing this will almost certainly result in better query performance than using a parameter/value attribute table. I say this because we have a vendor provided statistical process system build on just such a scheme. Inserts are great but query performance is not so great.

• Mark D Powell -- Phone (313) 592-5148

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ahbaid Gaffoor
Sent: Wednesday, February 06, 2008 10:52 PM To: oracle-l
Subject: Data Modeling Question

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 consider 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



**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of D&E except to the extent that it relates to their official business.
--
http://www.freelists.org/webpage/oracle-l

Received on Thu Feb 07 2008 - 09:49:08 CST