Entity-Attribute-Value (EAV) in Database Design Pattern

This post provides an overview of the Entity-Attribute-Value (EAV) database design pattern, its advantages, disadvantages, and use cases.

Table of contents

Intro

Entity-Attribute-Value (EAV) is a database design pattern that allows for flexible and dynamic data modeling. It is commonly used in scenarios where the number of attributes for an entity can vary significantly. This also helps object to extend with new capablities at runtime without modifying database.

The rigid Relational Approach: Code Analysis

Example database schema

This is the typical database design for Content Management System

// 1. CMS Video Entity
public class CmsVideo : BaseEntity
{
    public Guid Id { get; set; }
    public string Title { get; set; }
    public string Slug { get; set; }
    
    public bool IsDisplay { get; set; }
    public bool IsDisplayOnLandingPage { get; set; } 
    
    public List<CmsVideoPropertySetting> CmsVideoPropertySettings { get; set; }
}

// 2. CMS Blog Entity
public class Blog : AuditableEntity<Guid>
{
    public string Title { get; set; }
    public string Content { get; set; }
    public string Slug { get; set; }
    
    public bool IsDisplay { get; set; }
    
    public virtual PropertySetting Author { get; set; } 
    public virtual ICollection<BlogCategory> BlogCategories { get; set; }
}

// 3. Blog Permission Entity
public class BlogPermissionDetail : AuditableEntity<Guid>
{
  public Guid? BlogPermissionId { get; set; }
  public Guid? PropertySettingId { get; set; }
  public bool IsDeleted { get; set; }
  public virtual BlogPermission BlogPermission { get; set; }
  public virtual PropertySetting PropertySetting { get; set; }
}

// 4. Generalized PropertySetting attempting to serve multiple contexts
public class PropertySetting : Entity<Guid>
{
    public string Value { get; set; }
    
    public virtual ICollection<Blog> Blogs { get; set; }
    public virtual ICollection<Blog> IndividualBlogs { get; set; }
    public virtual ICollection<BlogCategory> BlogCategories { get; set; }
    
    public string DevelopmentStageName { get; set; }
    public int? From { get; set; }
    public int? To { get; set; }
    public AgeUnit? AgeUnit { get; set; }
}

Problem

While clean at first glance, this implementation harbors several hidden architectural anti-patterns that shows up as the application grows:

Here is a detailed analysis of the three major issues:

1. Explosion of Junction Tables (Violation of Open-Closed Principle)

Every time a new entity (such as Blog, CmsVideo, or BlogPermission) needs to associate with a setting, a new junction table/class is created (e.g., BlogPermissionDetail, CmsVideoPropertySetting, BlogCategory).

2. Semantic Mismatch (The authorId)

A configuration class currently hold properties such as Note, Logo, OrderNo, DevelopmentStageName. It should not contain a human represent which makes the code is confusing and unreadable.

3. Violation of Single Responsibility Principle (SRP)

The generic ProperSetting class contains columns that are highly specific to only one type of setting, make this field mostly null - the DevelopmentStage type.

// Inside PropertySetting.cs
public string DevelopmentStageName { get; set; }
public int? From { get; set; }
public int? To { get; set; }
public AgeUnit? AgeUnit { get; set; }

Entity-Attribute-Value (EAV) Design Pattern

Resolve the above issues

Building Entity

Refactor clean entities to avoid semantic issues, use only base attributes for entities.

Building Attribute Entity

public class AttributeDefinition : BaseEntity
{
    public Guid Id { get; set; }
    // e.g., "Author", "DevelopmentStageName", "AgeUnitFrom", "CmsVideo", "Blog"
    public string Name { get; set; } 
    // e.g., "Boolean", "String", "Integer", "DateTime" (used for parsing value at runtime)
    public string DataType { get; set; } 
    public string Description { get; set; }
}

Building Value Entity

public class EntityAttributeValue : BaseEntity
{
    public Guid Id { get; set; }
    // 1. Entity Identifier: Points to the ID of Blog, CmsVideo, or BlogPermission
    public Guid EntityId { get; set; }
    // 2. Discriminator: Stores the entity class type (e.g., "Blog", "CmsVideo", "BlogPermission")
    public string EntityType { get; set; } 
    // 3. Attribute Link
    public Guid AttributeDefinitionId { get; set; }

    public virtual AttributeDefinition AttributeDefinition { get; set; }
    // 4. Stored Value (Stored columns as String to parse at runtime)
    public string Value { get; set; } // This can avoid null columns in PropertySetting class and allow dynamic property at runtime 
}

Flexible object

Bringing EAV to the Application Layer ?

EAV is a database design pattern that allows for flexible and dynamic data modeling. We need a object or design pattern that can handle the extension of objects at runtime without modyfing code. This leads to a massive problem: How do we represent this dynamic data in our application layer code ?

What is a Flexible Object ?

• Flexible object is a design pattern that allows for the creation of objects with dynamic properties and behaviors. It is often used in scenarios where the structure of an object may change at runtime, or when the number of properties an object can have is not known in advance.
• It is built upon two core pillars:
  1. Dynamic State (Data Decoupling)
  2. Dynamic Behavior (Logic Decoupling)

Code Demo:

1. FlexibleBlog Implementation

public interface IBlogBehavior{
  object Execute(FlexibleBlog Blog, params object[]args);
}

public class FlexibleBlog{
  // Static property in Blog
  public Guid Id {get; set;}
  public string Title {get; set;}
  public string Content {get; set;}

  private readonly Dictionary<string, object> _properties = new Dictionary<string, object>();
  private readonly Dictionary<string, IBlogBehavior> _behaviors = new Dictionary<string, IBlogBehavior>();

  // Demo constructor
  public FlexibleBlog(){
    Id = Guid.NewGuid();
    Title = "Default Title";
    Content = "Default Content";
  }
  public void SetProperty(string key, object value){
    Console.WriteLine($"Setting property {key} to {value}");
    _properties[key] = value;
  } 
  
  // Dynamic binding property in Blog
  public T GetProperty<T>(string key){
    if(_properties.TryGetValue(key, out var value)){
      return (T)value;
    }
    throw new KeyNotFoundException($"Property {key} not found");
  }

  // Dynamic binding behavior in Blog
  public void SetBehavior(string key, IBlogBehavior behavior){
    Console.WriteLine($"Setting behavior {key}");
    _behaviors[key] = behavior;
  }

  public object ExecuteBehavior(string key, params object[] args){
    if(_behaviors.TryGetValue(key, out var behavior)){
      return behavior.Execute(this, args);
    }
    throw new KeyNotFoundException($"Behavior {key} not found");
  }
}

2. Behavior Implementation

public class ToStringBehavior: IBlogBehavior{
  public object Execute(FlexibleBlog Blog, params object[] args){

    string result = string.Empty;

    if (args.Contains("Author")) {
      result = result + Blog.GetProperty<string>("Author");
    }

    if (args.Contains("Category")) {
      var category = Blog.GetProperty<ICategory>("Category");
      result = result + (category != null ? category.Name : "No Category");
    }

    return result;
  }
}

3. Program.cs

using FlexibleObject;

FlexibleBlog blog = new FlexibleBlog();

blog.SetProperty("Author", "John Doe");
blog.SetProperty("Category", new BlogCategory("Technology"));
blog.SetBehavior("ToString", new ToStringBehavior());

Console.WriteLine($"Blog ID: {blog.Id}");
Console.WriteLine($"Blog Title: {blog.Title}");
Console.WriteLine($"Blog Content: {blog.Content}");
Console.WriteLine($"Blog Author: {blog.GetProperty<string>("Author")}");
Console.WriteLine($"Blog Category: {blog.GetProperty<ICategory>("Category").Name}");

string blogString = (string)blog.ExecuteBehavior("ToString", "Author");
Console.WriteLine($"Blog ToString: {blogString}");