Re: Data Modeling Question

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 cannot do this.

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