Postgresql – Form builder schema for SQL database?

database-designoptimizationpostgresqlschema

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?

Best Answer

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.