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.

Paul Horth

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

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

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

Does anything in the docs relating to XMLTYPE help you?

989611

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

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

> 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

AFAIK they have something similar in SQL server.

Etbin

It's not very clear (at least to me) whether or not the EAV approach might be appropriate in your case.

Just take a look at

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2584410600346445454

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2314483800346542969

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:26200054737635

As our italian neighbours say: Uomo avvisato, mezzo salvato. http://translate.google.com/#it/en/Uomo%20avvisato%2C%20mezzo%20salvato

Regards

Etbin