Design for the Data Pattern: Entity-Attribute-Value Model

by Zoja Savkovic

The Entity-Attribute-Value model is a common one in a lot of products. It allows you to add a key and a value pair to any "thing"/entity. It's often also called a custom field. Such as adding amenities to a hotel, or opening hours to a restaurant.

Another definition is that it is user-defined data field within a software system that allows users to store and organize additional information specific to their needs or business processes.

You can find examples in many places such as in JIRA, travel websites (listing custom fields for hotels for example) and a lot of customer relationship management (CRM) software.

In this post, let's discuss, how you would design a solution for this, at the high level.

Requirements

  • store key value pairs for an entity like storing that a pool (value) is an amenity (key) for a hotel (entity)
  • definitions to be set up by admin
  • values to be entered by user
  • values to be of many types such as text, date, boolean and multi list (both single and multi select)

Database Design

You could use any type of database. But let's say you are bound to a relational database.

One solution is to have a table for each of:

  • the custom field definition
  • the custom field value, if it's not a multi list
  • multi list options
  • for multi list custom fields, a bridging table to list chosen list options for a custom field

For tables, design the following for your use case:

  • have native data types for each of the data types (such as text, date, boolean) as this will prevent problems later with data integrity, and make data selects easier such as when selecting a date range with actual dates, rather than text that has to be cast to dates for a select
  • why data types were chosen and the length of each field
  • uniqueness and constraints
  • sorting and indexes

Design Considerations

You should limit the number of custom fields so that users

  • don't exploit it (for example if it's free as a data store). A higher limit could have a fee to prevent that if needed.
  • don't put a high load on infrastructure that it is not designed for
  • won't have a UI that was not designed for, say with thousands of custom fields on the page if it's not designed for that.

You should also validate the name of the custom field to ensure the name is not an existing name via blocklist, as otherwise it will look like a non custom field to the user on the UI (unless there is a visual difference for custom fields, but let's assume there is not).

Since deletion by accident is a big loss for customers in for example CRMs where they have put in effort to input the data - to protect against that you should, on delete, have a UI warning, and do soft delete (in addition to any audit tables).

For security, you should also sanitise input to protect against injection attacks.

For all these things be clear on who is making the decision, such as whether it is product or tech.