We are designing a large survey (~800 questions) database. We wanted
to have one answer table (that would be simple :-), but the problem
is, the answers are represented by multiple data types (int, text,
char, date, float). Each answer would only require one field for
storage, so one big answer table with a field for each type seems very
wasteful of storage.
The only solution we have come up with is an answer table for each
type. e.g.:
ans_int ans_char etc.
--------------- ------------------- ----
ansid serial... ansid serial...
answer int4... answer char(25)....
Each ansid will draw from a single sequence.
Any suggestions, comments?
P.S. Other questions
- MySql says that if one column in a table is varchar then ALL are
treated as such. Is PGSQL similar?
- Why does nextval(), for sequences, require an integer value to be
cast as text?