-- -- == CLEAN UP == -- drop table askers cascade; drop table answerers cascade; drop table questions cascade; drop table asker_questions cascade; drop table answer_values cascade; drop table answer_numeric cascade; drop table answer_date cascade; drop table answer_boolean cascade; drop table answer_float cascade; drop table answer_text cascade; drop table answer_date_2 cascade; drop table answer_numeric_2 cascade; drop table answers cascade; drop table atypes cascade; -- -- == TABLES == -- create table askers ( fid SERIAL PRIMARY KEY, fname text ); create table answerers ( orid SERIAL PRIMARY KEY, orname text ); create table atypes ( atype text , ncols smallint, PRIMARY KEY (atype, ncols) ); create table questions ( qid SERIAL PRIMARY KEY, question text, atype text, ncols integer, FOREIGN KEY (atype, ncols) references atypes (atype, ncols) ); create table asker_questions ( fid integer REFERENCES askers (fid), qid integer REFERENCES questions (qid), fset text, PRIMARY KEY (fid, qid, fset) ); create table answer_values ( orid integer REFERENCES answerers (orid), qid integer REFERENCES questions (qid), avid SERIAL, atype text, ncols integer, PRIMARY KEY (orid, qid, avid), FOREIGN KEY (atype, ncols) references atypes (atype,ncols) ); -- -- == CHILDREN ANSWER TABLES == -- create table answer_numeric ( avalue numeric ) inherits (answer_values) ; alter table answer_numeric alter column avid set default nextval('answer_values_avid_seq'); create unique index answer_num_pk on answer_numeric (avid); create table answer_date ( avalue date ) inherits (answer_values) ; alter table answer_date alter column avid set default nextval('answer_values_avid_seq'); create unique index answer_date_pk on answer_date (avid); create table answer_boolean ( avalue boolean ) inherits (answer_values) ; alter table answer_boolean alter column avid set default nextval('answer_values_avid_seq'); create unique index answer_bool_pk on answer_boolean (avid); create table answer_float ( avalue float ) inherits (answer_values) ; alter table answer_float alter column avid set default nextval('answer_values_avid_seq'); create unique index answer_flt_pk on answer_float (avid); create table answer_text ( avalue text ) inherits (answer_values) ; alter table answer_text alter column avid set default nextval('answer_values_avid_seq'); create unique index answer_text_pk on answer_text (avid); create table answer_date_2 ( astart date, aend date ) inherits (answer_values) ; alter table answer_date_2 alter column avid set default nextval('answer_values_avid_seq'); create unique index answer_dater_pk on answer_date_2 (avid); create table answer_numeric_2 ( astart numeric, aend numeric ) inherits (answer_values) ; create unique index answer_numr_pk on answer_numeric_2 (avid); alter table answer_numeric_2 alter column avid set default nextval('answer_values_avid_seq'); create table answer_addr ( addr text, city text, state char(2), zip text ) inherits (answer_values) ; alter table answer_addr alter column avid set default nextval('answer_values_avid_seq'); create unique index answer_addr_pk on answer_addr (avid);