Search code examples
databaserelational-databasestructure

Storing Entities with User-defined Components in Relational Database

I'm struggling to find the best way to store entities with user-defined fields. I would like to be able to do queries on these fields, so I feel NoSQL may not be the best approach. Constant schema migrations seems like a pain, especially since different users may want different fields on similar entities.

For example, let's say we have an entity representing a village. The village has a name (West Town), a type (village), a population (114). The user may want to add their own attributes to the village, say, a nickname. This is not known up front, and may not be required for other villages.

The best technique I've come up with is a table for the entities, and then a separate table for "components" of the entities, consisting of: a component id, a foreign key to the entity it's on, the name of the component, and its value.

So, the village from the example would exist as:

Table 1 - Entity
ID
1

Table 2 - String Components
ID ENTITY_ID NAME    VALUE
1  1         name    West Town
2  1         type    village

Table 3 - Integer Components
ID ENTITY_ID NAME       VALUE
1  1         population 114

Then, if the user wanted to add a "nickname" to the village, they could push a button, select a string component, call it "nickname" and give it a value of "Wesson":

Table 2 - String Components
ID ENTITY_ID NAME      VALUE
1  1         name      West Town
2  1         type      village
3  1         nickname  Wesson

Then, when the entity needs to be displayed, we query the component tables for the entity ID, and display the information:

name:       West Town
population: 114
type:       village
nickname:   Wesson

Is this crazy? It feels both sort of like an elegant way to represent a mutable schema in a relational database, and like trying to get around the whole point of a relational database. Is there a better way?

Solution

  • Answering my own question. This seems to generally be addressed using a pattern known as "entity-attribute-value" which is similar to what I've suggested.

    The entities table could be a little richer, storing also information common to all entities, like "name" and maybe a foreign key into an "entity_type" table.

    At its simplest, the attributes tables could be as above, with one for each data type.

    https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model