Designing an Attribute-Value-Store (AVS) Engine inside SQL for Dynamic Fields

Modern business apps often need to store dynamic attributes per entity: customer custom fields, product specifications, survey responses, feature flags, metadata, tags, etc. Hard-coding columns for every new attribute leads to constant schema changes and maintenance pain. An Attribute-Value-Store (AVS) (also called EAV — Entity-Attribute-Value) lets you store dynamic fields in a structured, queryable way inside SQL while preserving performance, integrity, and reporting capability.

This article gives a complete, production-grade blueprint for designing an AVS engine built on top of a relational database (SQL Server style), integrated with an ASP.NET Core backend and an Angular front-end. It covers data models, storage patterns, indexing, querying, validation, typed values, versioning, auditing, reporting strategies, caching, and pitfalls — from beginner to expert.

Goals & constraints

Design goals:

Support dynamic attributes per entity (products, customers, orders) without schema migrations.
Keep reads fast for common access patterns (filtering, sorting, projection).
Maintain data correctness (types, constraints, enumerations).
Provide good auditability and versioning.
Make it easy for Angular UI to render dynamic forms and for .NET to consume typed values.
Support search, reporting, aggregations, and export.
Scale to millions of attribute values.

Constraints & assumptions:

Primary DB is SQL Server (T-SQL), but patterns apply to Postgres/MySQL.
We prefer a mostly relational design (no pure JSONB-only approach), because of reporting and indexing needs.
We will use a hybrid approach — normalized metadata + typed value columns + JSON payload for complex cases.

High-level architecture

Angular UI <--> ASP.NET Core API <--> AVS Engine (SQL Server)
                                    |
                                    +--> Attribute Metadata (catalog)
                                    +--> Attribute Values (typed store)
                                    +--> Index & Materialized Views
                                    +--> Audit + Versioning tables
                                    +--> Background worker (re-indexing, compaction)

Key pieces:

Attribute Catalog — metadata for attributes (name, type, validation, options).
AVS Store — table(s) holding actual attribute values, typed columns for performance.
Index / Projection Tables — optional materialised tables for fast querying / reporting (denormalised).
API Layer — .NET services that expose typed model APIs and perform validation.
UI — Angular dynamic form generator reading catalog and building forms.
Background services — indexing, cleanup, schema migration helpers.

Workflow diagram (high level)

[Admin UI] -- define attribute metadata --> [AttributeCatalog Table] 
       |
       v
[Angular Form] <-- catalog API -------------- 
       |
user fills and submits  
       |
       v
[API] validate using catalog -> write to AVS store -> audit log
       |
       v
Optional: background job updates materialized projection for reporting

Flowchart: write & read paths

Write:
User submits -> API fetches attribute definitions -> Validate values -> Insert/Update typed AVS rows -> Insert Audit row -> Return success

Read:
UI requests entity + attributes -> API fetches attribute metadata -> API fetches AVS typed values (join) -> API transforms to DTO -> UI renders form

Data model — core tables

We will use a hybrid typed-value model to avoid excessive casting and improve indexing.

1. AttributeCatalog (metadata)

CREATE TABLE AttributeCatalog (
  AttributeId      BIGINT IDENTITY PRIMARY KEY,
  EntityType       VARCHAR(100) NOT NULL,   -- e.g., 'Product', 'Customer'
  AttributeName    VARCHAR(200) NOT NULL,
  DataType         VARCHAR(20) NOT NULL,    -- 'int','decimal','string','bool','datetime','json'
  IsRequired       BIT NOT NULL DEFAULT 0,
  IsSearchable     BIT NOT NULL DEFAULT 0,
  IsSortable       BIT NOT NULL DEFAULT 0,
  IsMultiValue     BIT NOT NULL DEFAULT 0,
  AllowedValues    NVARCHAR(MAX) NULL,      -- JSON array for enums
  ValidationRule   NVARCHAR(MAX) NULL,      -- optional JSON or expression
  DefaultValue     NVARCHAR(MAX) NULL,
  CreatedOn        DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
  CreatedBy        VARCHAR(200) NULL
);
CREATE UNIQUE INDEX UX_AttributeCatalog_Entity_Attr ON AttributeCatalog(EntityType, AttributeName);

2. AttributeValue (typed columns)

We use one row per attribute value (single-valued attributes). For multivalued attributes, multiple rows.

CREATE TABLE AttributeValue (
  ValueId          BIGINT IDENTITY PRIMARY KEY,
  EntityType       VARCHAR(100) NOT NULL,
  EntityId         BIGINT NOT NULL,
  AttributeId      BIGINT NOT NULL REFERENCES AttributeCatalog(AttributeId),
  ValueInt         BIGINT NULL,
  ValueDecimal     DECIMAL(28,8) NULL,
  ValueString      NVARCHAR(MAX) NULL,
  ValueBool        BIT NULL,
  ValueDateTime    DATETIME2 NULL,
  ValueJson        NVARCHAR(MAX) NULL, -- fallback for complex objects
  CreatedOn        DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
  CreatedBy        VARCHAR(200) NULL,
  ModifiedOn       DATETIME2 NULL,
  ModifiedBy       VARCHAR(200) NULL,
  IsDeleted        BIT NOT NULL DEFAULT 0
);
CREATE INDEX IX_AttrVal_EntityAttr ON AttributeValue(EntityType, EntityId, AttributeId);
-- Index searchable typed values:
CREATE INDEX IX_AttrVal_Int ON AttributeValue(AttributeId, ValueInt) WHERE ValueInt IS NOT NULL;
CREATE INDEX IX_AttrVal_Decimal ON AttributeValue(AttributeId, ValueDecimal) WHERE ValueDecimal IS NOT NULL;
CREATE INDEX IX_AttrVal_String ON AttributeValue(AttributeId, ValueString) WHERE ValueString IS NOT NULL;
CREATE INDEX IX_AttrVal_DateTime ON AttributeValue(AttributeId, ValueDateTime) WHERE ValueDateTime IS NOT NULL;

3. AttributeAudit (audit trail)

CREATE TABLE AttributeAudit (
  AuditId          BIGINT IDENTITY PRIMARY KEY,
  ValueId          BIGINT NULL,
  AttributeId      BIGINT NOT NULL,
  EntityType       VARCHAR(100) NOT NULL,
  EntityId         BIGINT NOT NULL,
  OldValueJson     NVARCHAR(MAX) NULL,
  NewValueJson     NVARCHAR(MAX) NULL,
  Action           VARCHAR(20) NOT NULL, -- 'INSERT','UPDATE','DELETE'
  PerformedBy      VARCHAR(200) NULL,
  PerformedOn      DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);
CREATE INDEX IX_AttributeAudit_Entity ON AttributeAudit(EntityType, EntityId, PerformedOn);

Why typed columns + fallback JSON?

Many EAV designs use a single Value column (nvarchar(max)). That simplifies the schema but kills indexing, sorting and numeric range queries. By having typed columns:

Numeric and date queries can use native indexes.
Sorting and aggregation are efficient.
We keep ValueJson column as fallback for complex objects (lists, nested structures).
The catalog tells which typed column to use for each attribute.

Read & write flow: details

write (create/update attribute value)

API receives entityId, entityType, and a payload of attributeName → value.
API fetches AttributeCatalog rows for given entityType and attributeNames (cacheable).
For each attribute:
- Validate type, required, allowed values, rules.
- Map value to correct typed column.
- If multi-value attribute, delete existing rows for entity+attribute and insert multiple rows.
- Upsert into AttributeValue (using MERGE or stored proc).
- Insert into AttributeAudit with old vs new JSON payload.

Example MERGE (simplified)

MERGE AttributeValue AS T
USING (VALUES (@EntityType, @EntityId, @AttributeId, @ValueInt, ..., @Now, @User)) AS S(EntityType, EntityId, AttributeId, ValueInt, ValueString, ModifiedOn, ModifiedBy)
  ON T.EntityType = S.EntityType AND T.EntityId = S.EntityId AND T.AttributeId = S.AttributeId AND T.IsDeleted = 0
WHEN MATCHED THEN
  UPDATE SET ValueInt = S.ValueInt, ..., ModifiedOn = S.ModifiedOn, ModifiedBy = S.ModifiedBy
WHEN NOT MATCHED THEN
  INSERT (EntityType, EntityId, AttributeId, ValueInt, ValueString, CreatedOn, CreatedBy) VALUES (S.EntityType, S.EntityId, S.AttributeId, S.ValueInt, S.ValueString, S.ModifiedOn, S.ModifiedBy);

read

API loads attribute catalog for the entityType (from cache).
Join AttributeValue filtered by EntityType and EntityId, return attributeName → typed value.
For Angular, produce a DTO:

{
 "entityId": 123,
 "attributes": [
   { "attributeName": "Color", "dataType": "string", "value": "Blue" },
   { "attributeName": "Weight", "dataType": "decimal", "value": 10.5 }
 ]}

Validation & business rules

AttributeCatalog stores ValidationRule and AllowedValues. Validation options:

Type checks — ensure value matches type.
Range checks — min/max for numbers and dates.
Regex — for strings (emails, phone).
Dependency rules — if attribute A = X then B must be present.
Custom rule script — stored as safe expression (JsonLogic, CEL) and executed in sandbox at API level. Avoid storing raw T-SQL for safety.

Implement validation in .NET

public ValidationResult Validate(AttributeCatalog meta, object value) {
  if (meta.IsRequired && value == null) return Error("required");
  switch(meta.DataType) {
    case "int": if (!int.TryParse(value?.ToString(), out _)) return Error("invalid int"); break;
    case "decimal": ... break;
    case "string": if (meta.ValidationRule != null) applyRegex(); break;
  }
  if(meta.AllowedValues != null) checkInEnum();
  // evaluate JsonLogic or CEL rule if present
  return Success();
}

Indexing & query patterns

Common queries:

Get all attributes for an entity. (fast via IX_AttrVal_EntityAttr)
Search entities where attribute X = Y. (use IX_AttrVal_String etc.)
Aggregate on numeric attributes (sum, avg).
Sort by dynamic attribute.

Pattern for search

SELECT av.EntityId
FROM AttributeValue av
JOIN AttributeCatalog ac ON av.AttributeId = ac.AttributeId
WHERE ac.EntityType = 'Product' AND ac.AttributeName = 'Weight' AND av.ValueDecimal BETWEEN 10 AND 20;

For multi-attribute filters combine multiple joins (or use aggregation):

SELECT e.EntityId
FROM Entities e
JOIN AttributeValue w ON w.EntityId = e.EntityId AND w.AttributeId = @weightAttrId AND w.ValueDecimal BETWEEN 10 AND 20
JOIN AttributeValue c ON c.EntityId = e.EntityId AND c.AttributeId = @colorAttrId AND c.ValueString = 'Red'

When queries become complex and frequent:

Build materialized projection tables per entity type with most used attributes as columns (background job updates).
Or maintain a search index (Elasticsearch) synchronized via change feed for full-text and multi-attribute queries.

Materialized projection (performance)

For fast reads and reporting, create ProductAttributeProjection:

CREATE TABLE ProductAttributes_Projection (
  ProductId BIGINT PRIMARY KEY,
  Color NVARCHAR(100),
  Weight DECIMAL(28,8),
  Price DECIMAL(28,8),
  -- few frequently used attributes as real columns
  LastUpdated DATETIME2
);

Maintain it via:

Triggers on AttributeValue (careful with performance), or
Background worker that listens to change table / queue and applies updates, or
SQL Server CDC + worker to apply deltas.

This provides fast SELECTs, range filters, ORDER BY, and aggregations.

Versioning & schema evolution

Track attribute versions so readers and processors can evolve gracefully.

Add CatalogVersion column in AttributeCatalog. Increment when metadata changes.
For values, add ValueVersion in AttributeValue if value schema changes.
Include timestamped AttributeAudit entries for each change.
For significant metadata changes (rename, type change), provide a migration tool:
- Convert existing ValueString -> ValueDecimal etc., store in temporary columns, validate migration correctness, then flip.

Store SchemaVersion at application level to understand how to interpret payloads.

Audit & immutability

Audit is mandatory for compliance:

Insert a row into AttributeAudit on every create/update/delete with old/new JSON snapshots.
Keep AttributeValue soft-deletions (IsDeleted) and never purge immediately.
Provide retention and purge policy as background jobs with legal hold awareness.

Concurrency & consistency

Concurrency options:

Use optimistic concurrency: check ModifiedOn or RowVersion before update.
Use stored procedures to implement atomic upsert + audit inside a single transaction.
For high contention attributes, consider per-entity locking (application-level semaphore) or Redis distributed lock.

Example SP (simplified):

CREATE PROCEDURE UpsertAttributeValue
  @EntityType varchar(100), @EntityId bigint, @AttributeId bigint, @ValString nvarchar(max), @User varchar(200)
AS
BEGIN
  SET XACT_ABORT ON;
  BEGIN TRANSACTION;
  DECLARE @old NVARCHAR(MAX) = (SELECT TOP 1 ... FROM AttributeValue WHERE ...);
  MERGE ...
  INSERT INTO AttributeAudit(AttributeId, EntityType, EntityId, OldValueJson, NewValueJson, Action, PerformedBy) VALUES (...);
  COMMIT TRANSACTION;
END

Background jobs & maintenance

Important background processes:

Projection updater — update materialized view/table.
Index optimizer — rebuild indexes periodically.
Compaction — clean old audit entries beyond retention, except legal holds.
Re-type migration — handle attribute type changes with safe migration scripts.
Search index sync — push changes to Elasticsearch.

Angular front-end: dynamic forms & UX

Angular responsibilities:

Load attribute catalog for given entityType via API: returns attributes with metadata (label, type, required, allowedValues, UI hints).
Render dynamic form components based on DataType and AllowedValues. Example mapping:
- string → text input
- int/decimal → number input
- bool → checkbox
- datetime → date picker
- json → nested form / JSON editor
- multiValue → multi-select
Client-side validation using same rules (required, regex, min/max). But always validate on server.
Submit typed payload: send attributeName→value pairs to API.

Example Angular DTO:

interface AttributeDto {
  attributeName: string;
  dataType: string;
  value: any;
}

interface EntityAttributesDto {
  entityId: number;
  attributes: AttributeDto[];
}

UI UX tips:

Show attribute descriptions/help text.
For enumerations, show friendly labels mapped from AllowedValues.
Apply lazy loading for many attributes (tabs / pagination).
Show last modified, who modified (from audit) on hover.

.NET API: service design

Keep service thin and focused:

IAttributeCatalogService — load catalog, cache, notify changes.
IAttributeValueService — validate, upsert, read, delete.
IAttributeProjectionService — projection updates.
IAttributeSearchService — search across entities (wraps SQL or ES).

Important implementation details:

Cache AttributeCatalog in memory with invalidation on admin updates (push via signal or simple TTL).
Use DTOs that translate typed DB columns into typed .NET types.
Use System.Text.Json + converters for ValueJson.

Sample upsert code (simplified):

public async Task UpsertAttributesAsync(long entityId, string entityType, IEnumerable<AttributeDto> attrs, string user)
{
  var catalog = await _catalogService.GetForEntityAsync(entityType);
  using var tx = _db.BeginTransaction();
  foreach(var a in attrs) {
     var meta = catalog.SingleOrDefault(x => x.AttributeName == a.AttributeName);
     var val = MapToTypedColumns(a.Value, meta.DataType);
     await _repo.UpsertAsync(entityType, entityId, meta.AttributeId, val, user);
  }
  await tx.CommitAsync();
}

Search & reporting recommendations

For complex filtering across many attributes, push attribute values to an external search engine (Elasticsearch). Use EntityId and attributeName/value fields for indexing.
Keep frequently queried attributes in projection tables to support SQL reports and BI tools.
Provide ETL processes that periodically dump dynamic attributes into data warehouse tables for analytics.

Scaling & performance tips

Keep hot attributes typed and indexed; avoid putting everything in ValueJson.
Denormalize frequently used attributes into projection table with column types.
Use partitioning on AttributeValue by EntityType or time for very large datasets.
Batch writes for bulk imports to reduce transaction overhead.
Use connection pooling and async DB calls.

Pitfalls & anti-patterns

Overuse of AVS: not all attributes should be dynamic. Use AVS for true variability; otherwise, prefer real columns.
Storing everything as JSON: kills indexing and queries.
Too many joins for queries: avoid complex multi-join filters for thousands of attributes; use projections/search.
No catalog governance: attribute sprawl occurs without naming conventions and deprecation policies.
Ignoring data types: using single NVARCHAR column everywhere sacrifices performance.
Triggers heavy: triggers cause unexpected performance and maintenance challenges; prefer background workers.

Migration & governance

Provide admin UI with attribute lifecycle: Draft → Review → Publish → Deprecate.
Version attribute definitions so consumers can adapt.
When changing type, implement migration utility that:
- Export current values, attempt conversion, write converted values to new typed column, and only flip when validation passes.
Maintain naming conventions and namespaces (e.g., product.color, product.spec.weight).

Example: use-case walkthrough

Scenario: Product entity with attributes: Color (string), Weight (decimal), Tags (multi string), Discontinued (bool), ReleaseDate (datetime).

Flow:

Admin defines these attributes in AttributeCatalog (set IsSearchable for Weight).
Angular product edit form fetches catalog, renders inputs.
User updates Weight to 10.5 → API validates decimal type and range → Upsert into AttributeValue with ValueDecimal = 10.5.
Background projection updates ProductAttributes_Projection.Weight = 10.5.
Sales report queries ProductAttributes_Projection to filter products with Weight between X and Y (fast).
Audit entry written for the Weight change.

Monitoring & observability

Track these metrics:

Number of attribute definitions per entity type.
Attributes written per minute (ingest rate).
Query latencies for attribute searches.
Projection lag (time between write and projection update).
Cache hit ratio for attribute catalog.
Storage size of AttributeValue table.

Log slow queries, failed validations, and projection errors. Use alerts for projection lag and sudden spikes.

Conclusion

An AVS engine inside SQL gives strong flexibility to support dynamic fields while preserving queryability, integrity, and performance — provided you design carefully:

Use a catalog for attribute metadata (types, validation, search flags).
Use typed columns for attribute values + JSON fallback for complex data.
Provide audit, versioning, and governance for attribute lifecycle.
Implement projections or external search for heavy reporting and search needs.
Keep validation at API (.NET) level and dynamic UI generation in Angular.
Avoid overusing AVS; choose it when attributes are truly variable.