Thread: What do you do when...? A schema Question
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?
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?
On 13 Jun 2001, holtzman wrote: > 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, I have written a mostly generic poll maker which mostly uses this structure: > The only solution we have come up with is an answer table for each > type. e.g.: The source code for the PostgreSQL definitions, and Perl poll creator/administrator/summarizer/replier is: http://www-internal.alphanet.ch/~schaefer/software.html look for `sondage'. Note that this is currently implemented in German (the user interface, that is, not the db structure nor comments). GPL