CODEWITHSUNDEEP
sqldatabase-design
Jacek
Jacek

Reputation: 211

Enable adding unknown properties to table objects

I am creating an database managment webapp that has a strange requirement to provide the user with ability to add new 'fields' to existing objects. For example the table 'Employees' has Names and ID's of emloyers. Suddenly the owner of system wants to know if his employees have driver license. But our table and app did not expect that

The only options that come to my mind is to

1) Add big varchar field storing aditional properties as JSON or something

2) Add table 'additional properties' That will allow creating new objects linked by PK to existing users

Then we will have

    TABLE USER         TABLE PROPERTIES
    -ID  <-------------FK
    -NAME              -NAME (driver livence)
                       -VALUE (true)

How bad is the second idea? Are there any better options other then going noSQL?

Upvotes: 0

Views: 27

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

There is no issue with either approach. EAV (entity-attribute-value) models have been part of relational databases, probably since the earliest databases were created.

They do have some downsides:

  • The value attribute is (generally) a string. That means that other types cannot be coerced into the column.
  • The values cannot be part of declared foreign key relationships.
  • Validating values is tricky -- very complicated check constraints, for instance.
  • There is no (easy) way to ensure that an entity has a particular value.

But for user-defined or sparsely populated values, EAV is definitely a reasonable choice.

JSON is another reasonable choice. That does, however, require a one-time change to the database to add a JSON column. Some databases offer indexing on JSON values, which can improve performance.

If "has-drivers-license" is a one-time change, then you might just want a separate table with the same primary key. The next time that a new column is needed, you can modify the "options" table, rather than the main table. This allows better support for validating values (all values are unique, for example) or defining foreign key constraints.

Upvotes: 2

Related Questions