Our database was created according to EAV (Entity-Attribute-Value) model. Individuals who've labored with EAV models understand all the garbage that accompany with regards to versatility.

I requested my client about why using EAV model (versatility), as well as their response was: Their organizations change with time. So, today they could have a table having a couple of characteristics, however in per month time, a couple of new characteristics might be added, or perhaps an existing attribute might be re-named. They have to produce reviews to return to any stage over time and query the information in line with the form of organizations at that point.

I realize this isn't achievable having a conventional relational model, however i personally see EAV as anti-pattern. Are there more alternative models that allows us to capture time dimension in changes towards the organizations and instances?

Cheers, Mosh

There's a noticeable difference between EAV done faithfully or badly 5NF made by skilled people or by individuals who're unaware.

Sixth Normal Form may be the Irreducible Normal Form (no further Normalisation can be done). It removes most of the damage that is common, like the Null Problem, and offers the best method determining missing values. It's the academically and technically robust NF. You will find no items to aid it, which is not generally used. To become implemented correctly and consistently, it takes a catalogue for metadat to become implemeneted. obviously, the SQL needed to navigate it might be much more cumbersome (SQL already being cumbersome re joins), but this really is easily overcome by automating producing SQL in the metadata.

EAV is really a partial set or perhaps a subset of 6NF. The issue is, usually it's accomplished for an objective (to permit posts to become added without needing to make DDL changes), by individuals who do not know the 6NF, and who don't implement metadata. The thing is, 6NF and EAV as concepts and ideas offer substantial benefits, and gratifaction increases but generally it's not implemented correctly, and also the benefits aren't realized. A number of EAV implementations are problems, not because EAV isn't good, but since the implementation is poor.

Eg. Many people believe that the SQL needed to create the 3NF rows in the 6NF/EAV database is complex: no, it's cumbersome although not complex. More essential, an regular SQL VIEW could be provided, to ensure that all customers and report tools see just the straight 3NF VIEW, and also the 6NF/EAV issues are transparent for them. Last, the SQL needed could be automated, therefore the work cost that lots of people endure is very unnecessary.

Therefore the answer is really, Sixth Normal Form, being the daddy of EAV, along with a purer form, may be the alternative for this. The Caveat is, make sure it is done correctly. I've one large 6NF db, also it suffers no problems people publish about, it works superbly, the cust is extremely happy (no further jobs are an indication of complete functional satisfaction).

I've already published a really detailed response to another question which is applicable for your question too, whhich you might be thinking about.

Other EAV Question

No matter the type of relational model you utilize, monitoring area title changes requires lots of meta data that you simply must keep an eye on either in transaction logs or audit tables. Regrettably, querying either of individuals for condition in a evening out is extremely complicated. In case your client only requires condition in a particular time date however, meaning the whole condition, not only regarding title changes, you are able to duplicate the database and roll back the transaction log towards the particular time needed and run your queries around the new instance. If organizations added following the specified date have to display in the query using the old area names however, you've got a large engineering problem in front of you. For the reason that situation, using the information you provided inside your question, I recommend either settling options using the client or getting good details about using the reviews to locate alternative solutions.

You can proceed to a document based datastore, but that also wouldn't solve the issue in the second situation. Sorry this is not really a solution, but getting labored through the same situation, the customer likely requires a more realistic confirming solution or many other traders prepared to front the main city for that engineering.

If this problem emerged for all of us, we stored the db schema constant and implemented an entity mapping factory with different timestamp. Ultimately, the customer constantly transformed needs (on the weekly to monthly basis) regarding how aggregate fields were calculated and were never fully satisfied.