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.

The Plan A


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?

  • I guess you're right, I, maybe, prematurely optimizing. I guess I don't like the idea of going back to board to fix a program model after it is finished. – Please_Dont_Bully_Me_SO_Lords Dec 3 '18 at 11:37
  • Unfortunately finished software is dead software. Personally I'd take pride in the fact that the system has evolved to the point that the original model no longer works. It means that my understanding of the problem has deepened, that there are people using my software, and that it is fulfilling a demand. Rewriting such a system would indeed be difficult, and I certainly don't disagree with laying plans now to make those rewrites easier, in fact planning is indispensable, just don't get hung up on the plans. – Kain0_0 Dec 3 '18 at 23:47
  • I am just about 6 years stuck with the plans... – Please_Dont_Bully_Me_SO_Lords Dec 3 '18 at 23:49

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.