Mysql polymorphic tables?

The needs would be long to describe, so I'll simplify the example.

I want to make a form creation system ( the user can create a form, adding fields, etc... ). Let's focus on chec开发者_开发知识库kbox vs textarea.

The checkbox can have a value of 0 or 1, depending on the checked status. The textarea must be a LONGTEXT type.

So in the database, that give me 3 choices concerning the structure of the table field_value:

1.

checkbox_value (TINYINT) | textarea_value (MEDIUMTEXT)

That mean that no input will ever use all column of the table. The table will waste some space.

2.

allfield_value (MEDIUMTEXT)

That mean that for the checkbox, I'll store a really tiny value in a MEDIUMTEXT, which is useless.

3.

tblcheckbox.value
tbltextarea.value

Now I have 1 separate table per field. That's optimal in terms of space, but in the whole context of the application, I might expect to have to read over 100 tables -- 1 query with a many JOIN ) in order to generate a single page that display a form.

In your opinion, what's the best way to proceed?

Do not consider an EAV data model. It's easy to put data in, but hard to get data out. It doesn't scale. It has no data integrity. You have to write lots of code yourself to do things that any RDBMS does for you if you model your data properly. Trying to use an RDBMS to create a general-purpose form management system that can accommodate any future needs is an example of the Inner-Platform Effect antipattern.

(By the way, if you do use EAV, don't try to join all the attributes back into a single row. You already commented that MySQL has a limit on the number of joins per query, but even if you can live within that, it doesn't perform well. Just fetch an attribute per row, and sort it out in application code. Loop over the attribute rows you fetch from the database, and populate your object field by field. That means more code for you to write, but that's the price of Inner-Platform Effect.)

If you want to store form data relationally, each attribute would go in its own column. This means you need to design a custom table for your form (or actually set of tables if your forms support multivalue fields). Name the columns according to the meaning of each given form field, not something generic like "checkbox_value". Choose a data type according to the needs of the given form field, not a one-size-fits-all MEDIUMTEXT or VARCHAR(255).

If you want to store form data non-relationally, you have more flexibility. You can use a non-relational document store such as MongoDB or even Solr. You can store documents without having to design a schema as you would with a relational database. But you lose many of the structural benefits that a schema gives you. You end up writing more code to "discover" the fields of documents instead of being able to infer the structure from the schema. You have no constraints or data types or referential integrity.

Also, you may already be using a relational database successfully for the rest of your data management and can't justify running two different databases simultaneously.

A compromise between relational and non-relational extremes is the Serialized LOB design, with the extension described in How FriendFeed Uses MySQL to Store Schema-Less Data. Most of your data resides in traditional relational tables. Your amorphous form data goes into a single BLOB column, in some format that encodes fields and data together (for example, XML or JSON or YAML). Then for any field of that data you want to be searchable, create an auxiliary table to index that single field and reference rows of form data where a given value in that respective field appears.

You might want to consider an EAV data model.