CREATE TABLE lbregistrationforms (
       formid BIGINT(20) NOT NULL AUTO_INCREMENT
     , locationid BIGINT(20) NOT NULL
     , formname TINYTEXT NOT NULL
     , formdescription TEXT
     , formcreatetext TEXT
     , formcompletetext TEXT
     , formregistrationstart DATE
     , formregistrationend DATE
     , formemail TINYTEXT
     , formsms TINYTEXT
     , formtimestamp TIMESTAMP
     , formaccesslevel BIGINT(20) NOT NULL
     , PRIMARY KEY (formid)
)TYPE=InnoDB;
ALTER TABLE lbregistrationforms MODIFY COLUMN locationid BIGINT(20) NOT NULL
      COMMENT 'location sensitive module';

CREATE TABLE lbregistrationformlists (
       listid BIGINT(20) NOT NULL AUTO_INCREMENT
     , locationid BIGINT(20) NOT NULL
     , listname TINYTEXT NOT NULL
     , PRIMARY KEY (listid)
)TYPE=InnoDB;
ALTER TABLE lbregistrationformlists MODIFY COLUMN locationid BIGINT(20) NOT NULL
      COMMENT 'location sensitive';

CREATE TABLE lbregistrationformfields (
       fieldid BIGINT(20) NOT NULL AUTO_INCREMENT
     , formid BIGINT(20) NOT NULL
     , fieldname TINYTEXT NOT NULL
     , fieldtype TINYTEXT NOT NULL
     , listid BIGINT(20)
     , fieldrequired TINYINT
     , fielddescription TEXT
     , fieldvalue TEXT
     , fieldorder BIGINT(20)
     , PRIMARY KEY (fieldid)
)TYPE=InnoDB;

CREATE TABLE lbregistrationformresponses (
       responseid BIGINT(20) NOT NULL AUTO_INCREMENT
     , formid BIGINT(20) NOT NULL
     , responsedatetime DATETIME NOT NULL
     , PRIMARY KEY (responseid)
)TYPE=InnoDB;

CREATE TABLE lbregistrationformlistentries (
       listentryid BIGINT(20) NOT NULL AUTO_INCREMENT
     , listid BIGINT(20) NOT NULL
     , listentryname TINYTEXT NOT NULL
     , PRIMARY KEY (listentryid)
)TYPE=InnoDB;

CREATE TABLE lbregistrationformfieldresponses (
       fieldresponseid BIGINT(20) NOT NULL AUTO_INCREMENT
     , responseid BIGINT(20) NOT NULL
     , responsevalue TEXT
     , fieldid BIGINT(20) NOT NULL
     , PRIMARY KEY (fieldresponseid)
)TYPE=InnoDB;

ALTER TABLE lbregistrationformfields
  ADD CONSTRAINT FK_lbregistrationformfields_1
      FOREIGN KEY (formid)
      REFERENCES lbregistrationforms (formid)
   ON DELETE CASCADE;

ALTER TABLE lbregistrationformfields
  ADD CONSTRAINT FK_lbregistrationformfields_2
      FOREIGN KEY (listid)
      REFERENCES lbregistrationformlists (listid);

ALTER TABLE lbregistrationformresponses
  ADD CONSTRAINT FK_lbregistrationformresponses_1
      FOREIGN KEY (formid)
      REFERENCES lbregistrationforms (formid)
   ON DELETE CASCADE;

ALTER TABLE lbregistrationformlistentries
  ADD CONSTRAINT FK_lbregistrationformlistentries_1
      FOREIGN KEY (listid)
      REFERENCES lbregistrationformlists (listid)
   ON DELETE CASCADE;

ALTER TABLE lbregistrationformfieldresponses
  ADD CONSTRAINT FK_lbregistrationformfieldresponses_2
      FOREIGN KEY (fieldid)
      REFERENCES lbregistrationformfields (fieldid)
   ON DELETE CASCADE;

ALTER TABLE lbregistrationformfieldresponses
  ADD CONSTRAINT FK_lbregistrationformfieldresponses_1
      FOREIGN KEY (responseid)
      REFERENCES lbregistrationformresponses (responseid)
   ON DELETE CASCADE;

