Forum Stats

  • 3,782,953 Users
  • 2,254,714 Discussions
  • 7,880,227 Comments

Discussions

EAV model related Query

989611
989611 Member Posts: 8
edited Jun 13, 2013 12:19PM in SQL & PL/SQL

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.

Answers

  • Paul  Horth
    Paul Horth Member Posts: 3,402 Gold Trophy

    Don't go there: it's not how you design an efficient system on a relational DB.

    Even the article you quote says

    "A major downside of EAV is its lower efficiency when retrieving data in bulk in comparison to conventional structure. In EAV model the entity data is more fragmented and so selecting an entire entity record requires multiple table joins.  What is more, when working with large volumes of EAV data, it can be necessary to transiently or permanently convert between columnar and row/EAV -modelled representation of the same set of data. The operation can be error-prone and CPU intensive."

    Also read: https://www.simple-talk.com/opinion/opinion-pieces/bad-carma/

    and you won't know whether to cry or laugh.

    Now, I'm not an XML expert but it is possible to store XML data inside a column of type XMLTYPE which has advantages like optimized handling of the XML and indexing

    See: http://docs.oracle.com/cd/E11882_01/appdev.112/e23448/t_xml.htm

  • 989611
    989611 Member Posts: 8

    Hi,

    Thanks for the response.

    Totally agree that - complex join queries would need to be written.

    Let me give some background details - our system is an even based messaging system that uses JBPM for orchestrating the events. Oracle is used in the back end.

    Each time the message in workflow enters the compareTrade node of workflow, we hit the database and return a 'heavy-weight' resultSet - that's converted to XML Dom objects

    and we do the matching and comparing. Issue we recently faced is - if volume of trades is high - JVM Heap is not able to withstand the load.

    Currently we iterate through the DOM objects and match values at xPath (not ideal).

    So the solution we are thinking is have a EAV model in schema, normalize the CLOB into EAV model.

    perform the 'basic' set of matching in Oracle via Query and return 'filtered' set of results - to do a 2nd round of matching.

    The 2nd round of matching will be light weight - since majoring number of Trades would have been filtered.

    Also - filtering done in Oracle will be 'fixed' i.e. clients don;t want to change the rules, while the 2nd set of filtering 'should' not be at database level - since the matching rules will be configurable at run time.

    and client may decide to "deploy the matching rules at run-time"

    So breaking down the XML into EAV seems to be better approach.


    regards,

    Deepak.

  • 989611
    989611 Member Posts: 8

    I must admit that I sent my reply before I had finished reading the entire article.

    I am not an Oracle expert but I am sure a lot must have changed since Oracle 7 in terms of handling large volume of data in a single table.

  • Paul  Horth
    Paul Horth Member Posts: 3,402 Gold Trophy

    Does anything in the docs relating to XMLTYPE help you?

  • 989611
    989611 Member Posts: 8

    Hi,

    Yes, read about XMLTYPE. Looks like Oracle has good support for XQueries. That looks promising.

    Only catch is - we sell our product as deploy-able in any SQL Dastabase. Will need to check if I get enough XML support in SQL Server as well.

    Thanks

    - Deepak

  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond

    This "solution" will scale as well as my goat can fly.

    You will soon discover that you are inside a deep, dark hole without any ladder to extricate yourself.

    Best Wishes!

  • Marwim
    Marwim Member Posts: 3,649 Gold Trophy

    > normalize the CLOB into EAV model.

    Isn't this a contradiction in terms, aka oxymoron :-)

    It's mainly not about the amount of the data but about the mess in your queries you will sooner or later get when you use EAV.

    I used EAV only once and only because any other solution would have been too academic for simple, seldom used and low volume data, where I knew that the data is accessed only in a single, previously known way.

    XML in a XMLTYPE column can be searched/filtered quite easily. Though I don't know your requirements for matching rules will be configurable at run time

    Regards

    Marcus

  • Paul  Horth
    Paul Horth Member Posts: 3,402 Gold Trophy

    AFAIK they have something similar in SQL server.

This discussion has been closed.