9 Replies Latest reply on Jun 13, 2013 4:19 PM by Etbin

    EAV model related Query

    989611

      All,

       

      I currently persist XML data (Trades) in my database (Trade table) as a CLOB column.

      And each time this data needs to be match/analysed - with new trade there is a performance impact.

       

      So I am considering EAV model.

       

      At the simplest level my table looks like this

       

      TRADE: (which we can assume is the Entity table)

       

      COLUMN                DATATYPE    

      --------------                -----------------              

      ID                          NUMBER(19,0)    

      TRADE_DATA        CLOB

       

      with the EAV model, I am planing to have these 2 additional Tables

       

      ATTRIBUTES

       

      COLUMN               DATATYPE

      --------------               ----------------

       

      attribute_id               NUMBER(19,0)

      name                       VARCHAR2(32 CHAR)

       

      ATTRIBUTE_VALUES

       

      COLUMN               DATATYPE

      -------------               -------------------

       

      value_id                    NUMBER

      attribute_id               NUMBER

      entity_id                    NUMBER

      value                         VARCHAR2(32 CHAR)

       

      attribute_id is foreign key for ATTRIBUTES table

      entity_id is foreign key for TRADE table

       

      The CLOB data will be normalized and stored in ATTRIBUTE_VALUES table.

      I consider this design after reading the link

      http://techportal.inviqa.com/2010/10/21/the-eav-data-model/

       

      My concern is - this extendable - once I have other different Tables storing CLOB data?

      i.e. would this design work with multiple entities table (the primary key in the other entity tables will be an auto-increment synthetic primary key)

       

      Or

       

      would I have need a Attributes/Attributes_values tables for each of my entity table? which sounds redundant.

       

       

       

      regards

      Deepak.