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.