.Net Architecture challenge: The Change-prone Frankestein Model

Question

Good Morning SO!

We've been scratching our heads with with this interesting scenario at the office, and we're anxious to hear your ideas and approaches:

1. We have a database, whose schema is prone to changes -lets call it Prony-. (is used to store configuration parameters for embedded devices. so if the embedded devices guy need a new table, property or relationship for the model, he should be able to adapt the schema in a easy way -happens so often- ).

2. Prony needs a web interface to create/edit its data (a set of simple CRUDs would be enough).

3. We have another database containing data that also need to be loaded to the devices, after making some transformations - lets call this one Oddy- (this data it's generated by an already existent administrative web application).

4. Finally we have Tracy, a server that communicates our DBs and our embedded devices. She should to auto-adapt herself, to our dbs schema changes and serialize the data to the devices.

Nice puzzle, don't think so? : )

---

Our current candidate:

• Rady: The fast

  Lets create some views in Prony that make the data transformation from Oddy. then use DynamicData (or some RAD tool) to create/update a simple web interface for Prony (so he can even consult the transformated data from coming from Prony : ). About Tracy, she will need to be recompiled to update her DB schema (Entity framework should work) and use Reflection to explore recursively the schema and serialize data.

  Cons:

  • We would have to recompile Tracy and the Prony's web interface.

What do you think of the candidate?

Any ideas?

Answer 1

One way to deal with a data model that is constantly changing after product release is to use the EAV Database Model, in whole or in part, of your database design. The EAV structure adds rows rather than columns or tables and allows for much less frequent schema changes (or none at all.)

It does come with its own set of caveats of course, such as the need to often pivot the data, but they can be managed. There are plenty of EAV dbs out there in production.

Performance note: People often worry about the performance of EAVs. i've have a number of EAVs with the EAV tables running well over 10 million records. Reads, inserts, updates and deletes are fine. The trouble comes in when you start doing heavy reporting on such a data structure. In your case you're storing config info for devices. That's not innocuous or trivial but I would say that that part of the db sounds like a great candidate for EAV since your reads and writes are going to be limited and i'm guessing that your reporting needs simple.