A table for each field type or one all-in-one table model?

Question

I am creating an application where users can create forms with many fields of text, numeric, date and select types.

1. The model will have a forms table referenced by responses table;
2. It also have the fields table with 'name', 'field_type' - it points to forms too;

3. The filled data will point to responses and to fields, but how should I model it:

   • A table for each field_type? (tables: text_fill, num_fill, date_fill, sel_fill); or
   • One table only with a column for each field_type where only one will be filled on each record?

My doubt is about performance, disk usage and industry standards. Can anyone clarify it to me with data?

Note 1: This is not a duplicate of Creating a online database creator because that question is about a generic database creator, and this questions is about form creator, which is bounded to an existent database model, which will be expanded by those forms, those bounded or by a single user or by a service that uses this database. Plus the answer on that question don't answers mine.

Answer 1

It depends on:

• the internal optimisations your database engine deploys.
• the relative size, indexing, clustering, and sharding of your tables.
• the power of the server you have.
• what normalisation of data is used.
• usage patterns.

Your current design is well decomposed, and looks to give you a low impedance between your applications object model and the way it is stored. Depending on the relative size of your key to payload your storage efficiency (space-wise) will vary but tend towards less efficient. Processing wise your field orientated operations are going to be simple (and quite likely fast), however your processing at higher-orders of abstraction (form-level, or survey-level) are going to be much harder, and probably slower.

If you instead manufacture normalised form specific tables, your space efficiency will improve, and so will working at a form/survey level. It will however make working at the field level more difficult, and probably slower.

You can of course use both techniques. This will give you both sets of advantages at the cost of increased complexity, and the need to maintain parity between both sets of data.

So the real question is, what is more important to the successful release of your application? Also, are you prematurely optimising?