0

I need to implement a custom fields in my database so every user can add any fields he wants to his form/entities.

The user should be able to filter or/and sort his data by any custom field.

I want to work with MySQL because the rest of my data is very suitable to SQL. So, unless you have a great idea, SQL will be preferred over NoSQL.

We thought about few solutions:

  1. JSON field - Great for dynamic schema. Can be filtered and sorted. The problem is that it is slower then regular columns. Dynamic indexes can solve that but is it too risky to add indexes dynamically.

  2. Key-value table - A simple solution but a really slow one. You can't index it properly and the queries are awful.

  3. Static placeholder columns - Create N columns and hold a map of each field to its placeholder. - A good solution in terms of performance but it makes the DB not readable and it has limited columns.

Any thoughts how to improve any of the solutions or any idea for a new solution?

Improve this question
14
  • Postgres allows indexes on JSON fields; however, we are not supposed to make software recommendations, so this is merely an observation. Commented Feb 12, 2018 at 11:44
  • MySQL as well, but do you recommend to add indexes dynamically? add index is a very heavy action which locks the table as far as I understand Commented Feb 12, 2018 at 11:46
  • @GuySegev - That's a question for you to answer. What is the burden of not indexing? You say the user should be able to sort or filter by those columns, doing so isn't ever free, but it is cheaper with appropriate indexing. The problem you're facing is that the S in SQL is Structured and that applies to the data as well as the language. The idea of dynamic columns that apply to some rows and not others is a description of unstructured or partially structured data. Commented Feb 12, 2018 at 11:59
  • 1
    An alternative is an EAV table ; Entity (a key to the real row you're adding data to), Attribute (a "name" for the column/attribute/field/data that you're adding to that row), Value (the, umm, value, that you're adding). Instead of adding columns, you add rows. But they are also slow when used for filtering or sorting. I suspect there is no good answer, only a least worst and that will depend heavily on your data, use cases, application, etc. Commented Feb 12, 2018 at 12:02
  • 1
    @GuySegev - It was already clear enough. My comments are not criticisms of your question, they're observations regarding how suitable SQL is (or is not) to your use-case. You can make it work, but you're going to have trade-offs to make. Those trade-offs are related to how many different columns you might have (i.e. 1000 users each having 5 bespoke columns each is very different to 5 admin users creating 5 columns shared by 1000 other users). They're related to how much data each user has (i.e. If a user only queries their own 100 rows of data, do you really need an index?). etc.. Commented Feb 12, 2018 at 12:52

1 Answer 1

Reset to default
1

As many of the commenters have remarked, there is no easy answer to this question. Depending on which trade-offs you're willing to make, I think the JSON solution is neatest - it's "native" to MySQL, so easiest to explain and understand.

However, given that you write that the columns are specified only at set up time, by technically proficient people, you could, of course, have the set-up process include an "alter table" statement to add new columns. Your database access code and all the associated view logic would then need to be configurable too; it's definitely non-trivial.

However...it's a proven solution. Magento and Drupal, for instance, have admin screens for adding attributes to the business entities, which in turn adds columns to the relational database.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.