1

First, I'll ask the question:

Is it possible to make a value's type 'dynamic' by creating different tables with different value types and use a simple join query to fetch the correct table based on the type specified in a column?

Here is the context:

I'm building a sort of page builder where I want the "modules" to be dynamic in the sense that I can create new modules without having to modify the database in any way.

I envision 4 entities:

  1. A field, which represents a single value of a specific type, for example a text field or a checkbox;

  2. An entry type, which represents a group of fields to be used for an entry. For example, Article = Title + Content + Image.

  3. An entry, which is the content defined by the corresponding entry type.

  4. A value, which contains the data for one field for one entry, defined by the field's value type.

For the 'value' entity, I was planning on using multiple tables for each "type of value". "value_text", "value_integer", "value_boolean", etc.

What I wanted to do (and I think it's an anti-pattern in relational database) was to grab a value in the fields table to select the proper "value" table.

Here is an image of the structure I envisioned: (imgur) UML Diagram of DB

So I'm looking for a way to make a single query to fetch the correct value for each field of a specific entry (by ID).

This means I have to access the assigned entry_type, fetch all the fields related to that entry_type, then fetch the value of each of those fields from the value tables based on the column 'value_type' of the fields table.

If this is possible, how can I achieve this? If it's not possible, what would be the best way to tackle this problem:

  1. Make a first query to retrieve the fields, then a query for each field to grab the correct value? This makes a lot of queries to get the values of a single entry...

  2. Modify the structure of the database to a more efficient set of relations, taking into account everything is dynamic...

  3. Get rid of the idea of using mutiple "value types" and stick to using a more common everything-is-serialized-text approach.

Also, as this is one of my first questions on StackOverflow, please let me know if I asked incorrectly and how I should formulate this in the future.

Thanks for all of you who even take the time to read this :)

Cedrik Dubois
  • 11
  • 2
  • I wouldn't use that design. As you say, it is an anti-pattern. You will not get good performance that way. Just think how many tables reading or writing a single object will touch. It would be better to create tables on the fly. But maybe you can come up with an alternative design, like storing "basic" attributes in a regular table and "extended" attributes that the database won't use much as a `jsonb`. – Laurenz Albe Feb 26 '19 at 17:38
  • @LaurenzAlbe I initially wanted to use a non-relational database like MongoDB for this, but I really want to keep support for shared hosts that support only php and mysql. If this idea of mine really doesn't work with mysql then it will become a limitation: Cannot use SQL DB. – Cedrik Dubois Feb 26 '19 at 17:47
  • I am confused. MySQL? You tagged "PostgreSQL". – Laurenz Albe Feb 26 '19 at 18:05
  • For future reference, this is a version of the Entity-Attribute-Value pattern: https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model – Michael Feb 26 '19 at 19:03
  • @LaurenzAlbe MySQL, PostgreSQL, SQLite, anything that works with conventional SQL. I'm currently using Postgresql as part of my development environment but that is not a requirement nor am I using any of it's specific features. – Cedrik Dubois Feb 26 '19 at 19:04
  • @LaurenzAlbe I've edited my tags to use MySQL, which is the only database I can afford to support, thanks for letting me know. One of my criteria is to run my app on shared host providers. I will not be able to use JSONB. But MySQL does support the JSON data with validation and quick index access. In my situation, I believe I could store the values in a JSON column in the entries themselves and hence avoid my current problem. I'll run some tests to see if it's faster than doing multiple queries. – Cedrik Dubois Feb 26 '19 at 19:24
  • 1
    @Michael that is a wonderful article, I'll take a read at it, I actually did not know how to call this situation of mine, so that's of great help to me, thanks! – Cedrik Dubois Feb 26 '19 at 19:24

0 Answers0