Hi PostgreSQL afficionados,
I have a little question that I know is not fully on topic, but
because I'm lurking this list I know many highly skilled and
experienced people are there. So I try it:
I have to store a big html form in my database. It has around 100
fields of differents kinds (varchars, dates, files, text). We have
done this before, in the 'square way', i.e. in a table with one col
(or more) for each field. With time and spec changes, the width of the
table grows up to 400 cols, and handling this become a headache. A
half of the cols are empty except for few exceptions. Col names are in
an incredible mess. Selecting in the table is complex and takes a long
time, even when we have only ~50 000 rows.
Now we start a new project, and I'm wondering if there is another way.
I've seen the unihan table, storing all the 70 000 unicode chinese
characters. Its shape is 'linear', i.e. only three cols:
~~~~~~~ this shape ~~~~~~~
codepoint, param, value
U+xxx, pinyin, ni
U+xxx, english_meaning, you
U+xxx, stroke_count, 7
[...]
U+yyy, pinyin, hao
U+yyy, english_meaning, good
U+yyy, stroke_count, 6
[...]
~~~~~~~ instead of ~~~~~~~
codepoint, pinyin, english_meaning, stroke_count, [...]
U+xxx, ni, you, 7, [...]
U+yyy, hao, good, 6, [...]
I'm wondering how I should choose between these two shapes. I suppose
both have pros and cons...
We plan to have around 10 000 new forms filled each month, and, even
if /they/ say that nothing will change, I know that we will have to
handle some slightly different html forms, and that /they/ will ask to
consolidate data (so I can't have a table for each 'kind' of form),
and that I need to think twice or trice before designing the thing. We
will do many selects on a few indexed field (id, name, etc), few
selects on unindexed fields, many inserts a day, only a few updates,
and no deletes.
I know using the 'linear' (not sure it's the name) shape will not
allow me easy strong typing, and because of the 'static' flavor of
unihan I can guess this shape is better for 'static' data, but still,
I had too many problems with traditional 'square'-shaped tables, and
want to make sure that no other solution is better.
Any help would be much appreciated, even a single link to some
interesting pages.
Guillaume
