I have an Entity-Attribute-Value (EAV) database in Oracle 11. For many, many reasons, I would like to model this using actual tables. Changing all the client code at once is not possible.
The existing tables look something like this (names changed to protect the guilty):-
entity_template- a master list of the possible entities. There are 23 of these.
entity_template_value- a master list of possible values which may be stored for a particular entity. This will become a column in the new solution. There are about 200 rows here.
entity- an individual instance of an entity. There are 60 million of these.
entity_value- the intersection between
entitywhich actually holds a value. There are 166 million of these.
All tables have surrogate primary keys.
I intend to create 23 new tables, one for each
entity_template. For example,
bar_entity, etc. Each of these will have the same primary key as
entity, which is a foreign key to
entity. The columns in these new tables will represent the rows from
I'll load these tables up with
INSERT /*+ APPEND */ and we'll suffer an outage while this happens.
When a row is inserted into
entity, a new row will be inserted into the appropriate child table. All columns besides the primary key will be null.
entity_value with a view. The view will be have about 200 UNIONS, where each one selects a column from one of the 23 new tables. An
INSTEAD OF trigger will be written to translate inserts/updates to updates on one column in one of the new tables. Deletes on
entity_value will become updates to NULL of one column on one of the new tables.
Then progressively, client code can be written to access the new tables directly. One glorious day, the view can be dropped and we can forget it was ever like this.
Are there any flaws in this approach, or can you suggest any improvements?