Skip to content

Instantly share code, notes, and snippets.

@firmanelhakim
Created July 13, 2017 07:11
Show Gist options
  • Save firmanelhakim/8666f9bd624dfbb0d00522fd318a602c to your computer and use it in GitHub Desktop.
Save firmanelhakim/8666f9bd624dfbb0d00522fd318a602c to your computer and use it in GitHub Desktop.
Dynamic Forms Builder using PostgreSQL
CREATE TABLE master.forms
(
form_id serial NOT NULL,
code CHARACTER VARYING,
name CHARACTER VARYING,
description CHARACTER VARYING,
properties jsonb,
audit_trails jsonb,
stereotype CHARACTER VARYING,
sort_order INTEGER,
row_status CHARACTER VARYING DEFAULT 'ACTIVE'::CHARACTER VARYING,
created_by INTEGER DEFAULT 0,
created_on TIMESTAMP WITH TIME ZONE DEFAULT now(),
lastupdate_by INTEGER,
lastupdate_on TIMESTAMP WITH TIME ZONE,
CONSTRAINT pk_forms PRIMARY KEY (form_id)
);
CREATE TABLE master.fields
(
field_id serial NOT NULL,
parent_id INTEGER,
form_id INTEGER,
field_type_id INTEGER,
code CHARACTER VARYING,
name CHARACTER VARYING,
properties jsonb,
audit_trails jsonb,
stereotype CHARACTER VARYING,
sort_order INTEGER,
row_status CHARACTER VARYING DEFAULT 'ACTIVE'::CHARACTER VARYING,
created_by INTEGER DEFAULT 0,
created_on TIMESTAMP WITH TIME ZONE DEFAULT now(),
lastupdate_by INTEGER,
lastupdate_on TIMESTAMP WITH TIME ZONE,
CONSTRAINT pk_fields PRIMARY KEY (field_id),
CONSTRAINT fk_field_has_type FOREIGN KEY (field_type_id) REFERENCES master.field_type (field_type_id) MATCH SIMPLE ON
UPDATE CASCADE ON
DELETE RESTRICT,
CONSTRAINT fk_form_has_fields FOREIGN KEY (form_id) REFERENCES master.forms (form_id) MATCH SIMPLE ON
UPDATE CASCADE ON
DELETE CASCADE,
CONSTRAINT fk_ptsp_form_fields_has_parent FOREIGN KEY (parent_id) REFERENCES master.fields (field_id) MATCH SIMPLE ON
UPDATE RESTRICT ON
DELETE CASCADE
);
CREATE TABLE master.field_type
(
field_type_id serial NOT NULL,
code CHARACTER VARYING,
name CHARACTER VARYING,
properties jsonb,
audit_trails jsonb,
stereotype CHARACTER VARYING,
sort_order INTEGER,
row_status CHARACTER VARYING DEFAULT 'ACTIVE'::CHARACTER VARYING,
created_by INTEGER DEFAULT 0,
created_on TIMESTAMP WITH TIME ZONE DEFAULT now(),
lastupdate_by INTEGER,
lastupdate_on TIMESTAMP WITH TIME ZONE,
CONSTRAINT pk_field_type PRIMARY KEY (field_type_id)
);
CREATE TABLE asset.master_data
(
asset_id serial NOT NULL,
form_id INTEGER,
properties jsonb,
audit_trails jsonb,
stereotype CHARACTER VARYING,
sort_order INTEGER,
row_status CHARACTER VARYING DEFAULT 'ACTIVE'::CHARACTER VARYING,
created_by INTEGER DEFAULT 0,
created_on TIMESTAMP WITH TIME ZONE DEFAULT now(),
lastupdate_by INTEGER,
lastupdate_on TIMESTAMP WITH TIME ZONE,
CONSTRAINT pk_master_data2 PRIMARY KEY (asset_id),
CONSTRAINT fk_data_has_form_id FOREIGN KEY (form_id) REFERENCES master.forms (form_id) MATCH SIMPLE ON
UPDATE RESTRICT ON
DELETE SET NULL
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment