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:
- Performance - typically slow performance/complex queries when attempting to list features together with attributes
- 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?