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.

Existing Tables

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 entity_template_value and entity which actually holds a value. There are 166 million of these.

All tables have surrogate primary keys.

Target Tables

I intend to create 23 new tables, one for each entity_template. For example, foo_entity, 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 entity_template_value.

I'll load these tables up with INSERT /*+ APPEND */ and we'll suffer an outage while this happens.

Maintaining Compatibility

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.

I'll replace 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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.