As a follow-up, we are now (not that I totally agree, but I am part of
larger group) going to store ALL data in a single table as TEXT. The
presumption here is that the data will arrive via html forms on a web
page, thus the data is originated as text. So, we'll store it as such
and cast it to its appropriate type whenever we need to access it.
Keep in mind, however, some of this is scientific/medical data (e.g.
blood lab results).
I'd still like to see any comments/suggestions, as well as any answers
to the additional questions.
Thanks!
holtzman
junkboy@zeus.bwh.harvard.edu (holtzman) wrote in message news:<e9dce49a.0106131430.55b1827@posting.google.com>...
> 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?
