RE: Data Modeling Question
- From: "Elliott, Patrick" <patrick.elliott@xxxxxxxxxxxxx>
- To: "ahbaid@xxxxxxx" <ahbaid@xxxxxxx>, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 7 Feb 2008 14:07:37 -0600
Option 1 is not what you want. If you look in Tom Kyte's book "Effective
Oracle by Design" on page 34, you will see why it is BAD.
Pat
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Ahbaid Gaffoor
Sent: Wednesday, February 06, 2008 9: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
--
//www.freelists.org/webpage/oracle-l
[CONFIDENTIALITY AND PRIVACY NOTICE]
Information transmitted by this email is proprietary to Medtronic and is
intended for use only by the individual or entity to which it is addressed, and
may contain information that is private, privileged, confidential or exempt
from disclosure under applicable law. If you are not the intended recipient or
it appears that this mail has been forwarded to you without proper authority,
you are notified that any use or dissemination of this information in any
manner is strictly prohibited. In such cases, please delete this mail from your
records.
To view this notice in other languages you can either select the following link
or manually copy and paste the link into the address bar of a web browser:
http://emaildisclaimer.medtronic.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
