Suggested method for “simplifying” EAV

Question

I am working on a GIS platform that basically handles roads with features. The system uses an EAV model to store all the data in a database. The system has been around for a while and there is nothing I can do to completely change the database model. EAV was chosen because each installation had unique features with unique attributes.

We would like to create API's (eg a Web Api) and database views on top of this platform for easy integration with other systems - primarily read only.

The challenge we are facing today when designing the API's is mainly two things:

1. Performance - typically slow performance/complex queries when attempting to list features together with attributes
2. Searchability - hard to perform a query with the correct types (surface = 'gravel' and width > 2) because everything is stored as strings in the database

Ideally, we would like to have a set of tables in the database, one for each feature with the correct columns and data types.

One idea is to use triggers on the database to create and maintain a separate schema containing these tables with the correct columns (one for each type of feature). This schema would have to re-create affected portions eg if an attribute is added or removed from a certain type of feature. Creating these triggers is not a trivial task.

Does anyone else have a better idea?

Answer 1

This is not a better idea than the one you suggest. It just fleshes it out a little more.

There are many situations where business use of the data results in two databases. One is transaction oriented and the other is analysis oriented. This second database is sometimes called a reporting database, a data mart, or even a data warehouse. The process of keeping the reporting database up to date is called Extract, Transform, and Load (ETL). ETL can be done on a roll your own basis, or there are ETL tools that can be applied to many situations. These tools are non trivial.

There are two differences between your case and the cases I have actually worked. The first is that your transaction database is EAV, while the cases I have worked started with a relational transaction database. Second, you are trying to make the update process event driven through triggers, while the cases I worked used an overnight update job, and allowed the reporting database to lag behind current data by a day or even up to a month. ETL is non trivial in the best of circumstances, and your case presents unique challenges.

If you are interested in a specific layout for tables that will make a wide variety of reports or extracts easy to produce, I'm going to recommend that you look into a design pattern called "star schema". A star schema basically starts with a multidimensional model of the subject matter, and layers it onto the kind of SQL tables that are typically used to build relational databases. There is a similar pattern called snowflake schema where some of the tables in a star schema have been normalized to some degree.

There's a lot to star schema or snowflake schema, and I'm not going to try to summarize it here.

Happy hunting!