c# - If EAV is evil, what to use for dynamic values?

Question

I need to create database where Accountgroup table will have dynamic fields so that Accounts can enter those dynamic field values when needed. It's probably not important but I'm using C# with EF and Linq.

It's hard for me because I never done anything like that and since I did my research everyone is saying that EAV systems are horrible and you should design it differently, the problem is that nobody tells afterwards - how?

So maybe you can help me out and tell how can I implement something similar without doing EAV?

This is what I have so far.

2020 Edit:

I just want to edit this post because it's 2020 and there are clear answers to this: Postgres with jsonb type. EF Core supports it so you can actually save and query dynamic json without any problems.

Answer 1

The problem with rules of thumb is that they quickly go from "It is usually a bad idea to do X" to "Never do X".

EAV is generally a bad idea because in many ways it defeats the purpose of a relational schema and thereby it takes away many of the features and advantages of a relational DBMS, and other technologies built on RDBMS, such as ORMs like Entity Framework.

However, there are certain design problems for which RDBMS isn't a great fit. There are some that are such a bad fit that a whole new technology had to be invented (e.g. NoSQL DB like MongoDB).

There are times when EAV is probably the best choice left to you out of a set of imperfect options. If you don't (can't) know what your schema is before hand, then EAV may be your best choice. This is especially true if your schema turns out to be unimportant. Consider for example an online product catalog where you have a huge list of products, each of which has some number of features. You can't predict in advance which products will have which features. And in the end, the only thing you do with product features is dump them out in a "feature: value" list anyway. This is a situation where schema isn't especially powerful, so defeating it with EAV isn't especially damaging.

The most important thing is to understand what your design choices are going to do to your capabilities and operations. All design is trade-off. The point is to make your trade-offs consciously. Instead of "EAV is Evil", think instead: "EAV is a loaded gun, make sure you know whose foot you're pointing it at."