The Entity-Attribute-Value Model

The entity-attribute-value model is useful for situations where attributes are dynamically added to or removed from an entity. It is normally composed of three tables:

Entity Attribute Value Logical Model 1
Here’s an example of data in the model:

Entity Attribute Value Data Example
You’ll notice in this example that each entity does not have all the attributes, or the same attributes as some other entities, or even any attributes at all. These are dynamic relationships, created on the fly by users in the front-end application. This is the scenario that the Entity-Attribute-Value model is meant for.

In the example above the primary key of EntityAttributeValue is {EntityId, EntityAttributeId}, which means that each entity can have a particular attribute 0 or 1 times. This may be undesirable for your business case, for example if your entities were people they could legitimately have 0 or 1 or many phone numbers. If this is the case, you can assign a surrogate key to EntityAttributeValue:

Entity Attribute Value Logical Model 2

This will support a zero-to-many relationship for entities to attributes.

Because the attributes are dynamic, their value types are dynamic also. In this example I set the Value column to varchar(128) so that a wide variety of values can be placed into in. Users can and will store text and numeric data in the Value column.

This can sometimes be a stumbling block if you have an application that needs to use values from the EntityAttributeValue table. There might be a “freight rate” attribute, for example, that your application needs to include in a calculation. But because it is a varchar field it could contain non-numeric data.

In this case you can create multiple value columns with different specific types. This way the attribute’s value type can be specified as ‘numeric’, and your application will only check the appropriate value column.

Entity Attribute Value Logical Model 3

There is some criticism for this model. It can sometimes be tempting to run wild with it, thinking that this is generic enough that anything can fit, so why create a bunch of unnecessary tables. And so it sometimes becomes a one-size-fits-all model, ripe for the inner-platform effect.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s