4

There are many ways one can envision the solution, from the top of my head:

  • Two table approach:
    • Field table with (name, field_name, type, description, meta) attributes
    • Form table with (name, created_by) [name being foreign-key]
  • Code-generation approach:
    • New tables created on the fly

Example of what will come in from the user (e.g.: via an HTTP POST):

{
    "name": "dinner_time",
    "fields": [
        {"field_name": "hungry", "type": "boolean", "description":"", "meta": [""]]},
        {"field_name": "time", "type": "datetime", "description":"time now", "meta": [""]]}
    ],
    "created_by": "AT"
}

How should I enable this use-case, and what should I be extra wary about?

3
  • Unfortunately this is something relational databases are really quite bad at. Dynamic user-defined data with variable structure. Defining the form structure is only half your problem; where it gets ugly is storing the form results. I'd seriously consider just using json representation all the way through, whether with PostgreSQL's json support or using a different product that might be better suited to variable-structured data. Commented Sep 7, 2015 at 3:45
  • But that'd result in a huge performance hit. Let's say a form is created, and millions of submissions against it are recorded. Without indices and other db features, answering aggregate and even simple SELECT queries would become really inefficient. I'm not talking about exposing a whole Microsoft Access-esque interface, but a simple form builder (with PK, FK and indexes) shouldn't be nontrivial to create.
    – A T
    Commented Sep 13, 2015 at 4:48
  • I agree it shouldn't be hard. As I said it's an area RDBMSes pretty much suck at. If you aren't doing lots and lots of different forms you could create tables with dynamically app-generated schemas. This doesn't tend to scale well to tens of thousands of forms (tables) though. Commented Sep 13, 2015 at 5:02

1 Answer 1

0

It's doable and quite safe approach is

  • Store form definitions in form_type table with JSONB and proper indexing. The table should have a marker telling if the form is already SQL-ized with create table
  • Write a asynchronous processing tool (cron worker) which will update table structure and issue proper CREATE TABLE requests, marking form_typeas deployed to SQL
  • form submit requests can be issued only after the form table is deployed
  • you can and should limit permissions / quota for this deployment task

Why async? I would avoid doing this in trigger due to locking issues under high load. Also, easy to DDOS so permissions should be separate for form insert and form create.

1
  • Thanks @filiprem ; I've been thinking of this, is there a way of faking the entity SELECT/UPDATE/INSERT operates on to be a generic JSONB? - Fallback instead of relation "a" does not exist; then later data can be migrated into real tables that are created asynchronously and drained/pulled asynchronously…
    – A T
    Commented Jul 13, 2017 at 3:28

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