CODEWITHSUNDEEP
sqldatabasedatabase-designnosqldatabase-schema
Kieran Oldham
Kieran Oldham

Reputation: 36

Custom user defined database fields, what is the best solution?

To keep this as short as possible I'm going to use and example.

So let's say I have a simple database that has the following tables:

  1. company - ( "idcompany", "name", "createdOn" )
  2. user - ( "iduser", "idcompany", "name", "dob", "createdOn" )
  3. event - ( "idevent", "idcompany", "name", "description", "date", "createdOn" )

Many users can be linked to a single company as well as multiple events and many events can be linked to a single company. All companies, users and events have columns as show above in common. However, what if I wanted to give my customers the ability to add custom fields to both their users and their events for any unique extra information they wish to store. These extra fields would be on a company wide basis, not on a per record basis ( so a company adding a custom field to their users would add it to all of their users not just one specific user ). The custom fields also need to be sesrchable and have the ability to be reported on, ideally automatically with some sort of report wizard. Considering the database is expected to have lots of traffic as well as lots of custom fields, what is the best solution for this?

My current research and findings in possible solutions:

  1. To have generic placeholder columns such as "custom1", "custom2" etc. ** This is not viable as there will eventually be too many custom columns and there will be too many NULL values stored in the database

  2. To have 3x tables per current table. eg: user, user-custom-field, user-custom-field-value. The user table being the same. The user-custom-field table containing the information about the new field such as name, data type etc. And the user-custom-field-value table containing the value for the custom field ** This one is more of a contender if it were not for its complexity and table size implications. I think it will be impossible to avoid a user-custom-field table if I want to automatically report on these fields as I will have to store the information on how to report on these fields here. However, In order to pull almost any data you would have to do a million joins on the user-custom-field-value table as well as the fact that your now storing column data as rows which in a database expected to have a lot of traffic as well as a lot of custom fields would soon cause a problem.

  3. Create a new user and event table for each new company that is added to the system removing the company id from within those tables and instead using it in the table name ( eg user56, 56 being the company id ). Then allowing the user to trigger DB commands that add the new custom columns to the tables giving them the power to decide if it has a default value or auto increments etc. ** Everytime I have seen this solution it has always instantly been shut down by people saying it would be unmanageable as you would eventually get thousands of tables. However nobody really explains what they mean by unmanageable. Firstly as far as my understanding goes, more tables is actually more efficient and produces faster search times as the tables are much smaller. Secondly, yes I understand that making any common table changes would be difficult but all you would have to do is run a script that changes all your tables for each company. Finally I actually see benefits using this method as it would seperate company data making it impossible for one to accidentally access another's data via a potential bug, plus it would potentially give the ability to back up and restore company data individually. If someone could elaborate on why this is perceived as a bad idea It would be appreciated.

  4. Convert fully or partially to a NoSQL database. ** Honestly I have no experience with schemaless databases and don't really know how dynamic user defined fields on a per record basis would work ( although I know it's possible ). If someone could explain the implications of the switch or differences in queries and potential benefits that would be appreciated.

  5. Create a JSON column in each table that requires extra fields. Then add the extra fields into that JSON object. ** The issue I have with this solution is that it is nearly impossible to filter data via the custom columns. You would not be able to report on these columns and until you have received and processed them you don't really know what is in them.

Finally if anyone has a solution not mentioned above or any thoughts or disagreements on any of my notes please tell me as this is all I have been able to find or figure out for myself.

Upvotes: 0

Views: 2558

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

A typical solution is to have a JSON (or XML) column that contains the user-defined fields. This would be an additional column in each table.

This is the most flexible. It allows:

  • New fields to be created at any time.
  • No modification to the existing table to do so.
  • Supports any reasonable type of field, including types not readily available in SQL (i.e. array).

On the downside,

  • There is no validation of the fields.
  • Some databases support JSON but do not support indexes on them.
  • JSON is not "known" to the database for things like foreign key constraints and table definitions.

Upvotes: 1

Related Questions