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

    EAV model related Query




      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




      COLUMN               DATATYPE

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


      attribute_id               NUMBER(19,0)

      name                       VARCHAR2(32 CHAR)




      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



      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)




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