11

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.

enter image description here

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.

Stan
  • 25,744
  • 53
  • 164
  • 242
  • 1
    Please put the answer in an answer post, not the question post. – philipxy Aug 26 '20 at 00:10
  • Even after your 2020 Edit, it will be hard for anyone to give design recommendations based on one line sentence problem statement. It seems you managed to find a solution for a situation where you're dealing with an unknown set of fields. You at least knew two entities 'AccountGroup' and 'Account'. Generally you want to know more to design appropriately because at this point, you could be capturing just anything against 'an item that goes into a group'. C#'s simple dynamic collections alone would have happily serialized this for you into a json list, so no real data modelling involved. – bizl Jan 05 '22 at 01:36
  • @bizl This was more of a database-design question. I could not just store the objects in memory. – Stan Jan 05 '22 at 22:19

4 Answers4

24

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."

Joel Brown
  • 14,123
  • 4
  • 52
  • 64
  • "the only thing you do with product features is dump them out in a "feature: value" list anyway" - But what if want to filter products by those features? It implies that those features have to appear in product type filters. – 7cart project Dec 13 '18 at 16:56
  • @dmytrocx75 The way that this would be handled by the more sophisticated online product catalogues would be for products to be classified into categories and for these categories to each have their own list of "suggested" or "typical" features. Browse any online store and you'll see that even when they try to do this it's only with limited success. Still, you could get fancy and have queries that detect which features are well populated - and even better - reasonably selective. Then you could offer these features with sample values as side-bar filters (for example). – Joel Brown Dec 14 '18 at 02:39
  • It's not an answer. Here is my similar question https://dba.stackexchange.com/questions/224603/help-finding-compromise-between-id-and-key-referencing – 7cart project Dec 14 '18 at 13:46
3

Well, at the simplest level, just add the values as columns; perhaps using the sparse column support at the database so that it doesn't have much size impact. This avoids both EAV and the inner-platform effect, and means you are storing the values as regular, typed values.

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • Well I can't really create new keys on sparse columns on the fly, can I? – Stan Jun 21 '13 at 09:55
  • @Steve well, you *can* (and indeed I *do* - oddly enough I have such a one in front of me right this moment); the problem is that it doesn't play nicely with ORMs if the ORM demands the columns at build-time, and the data is defined at run-time. There's all *kinds* of crazy meta-programming you can do here; at one end of the spectrum, your domain entity can be `abstract`, with you generating the subclass on-the-fly (`TypeBuilder`) when you know the extra values, and then using a runtime type-aware ORM to work with it. – Marc Gravell Jun 21 '13 at 10:00
  • @Steve to rephrase that; EF is a tool designed to fit one category of problems; the "dynamic fields" problem is a *different* category, that isn't quite what EF targets. You have three options: use EAV; or use smoke and mirrors to trick EF (in particular EF code-first); or use a different tool – Marc Gravell Jun 21 '13 at 10:02
  • So my best option if I don't want to use EAV is to use raw queries, right? – Stan Jun 21 '13 at 10:04
  • @Steve well, that is "an" option. I'm hesitant to use the word "best" here. – Marc Gravell Jun 21 '13 at 10:05
  • And what would you recommend in my example? I have very little db-design experience. – Stan Jun 21 '13 at 10:12
  • 2
    @Steve that would depend a lot on your system, unfortunately. There are very few simple answers in design. It could well be that EAV is the most reasonable choice. – Marc Gravell Jun 21 '13 at 10:51
3

EAV is not "evil" - it just sometimes gets misused when other solutions might be more appropriate.

If your attributes are truly dynamic and you want to avoid dynamically adding columns1, then EAV is appropriate.

1 E.g. to avoid locking the table or because your ORM of choice doesn't play well with it or because there is simply too many of them.

Community
  • 1
  • 1
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
2

When you start using EAV fields for reports or BI, you will want to shoot yourself in the face.

  1. Use an XML field. Most databases have good XML support, XPath queries, indexing.

  2. Create a new schema for a tenant's user-designed fields. Let the user do what they want with that schema (within reason).

For example 

create table account_group (
  id primary key references real_schema.accountgroup(id),
  super_important_note_field text not null
);

Yes, you can do foreign keys across schemas.

Neil McGuigan
  • 46,580
  • 12
  • 123
  • 152