pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedconstants in 2-column foreign keys or how to design a storage for text-groups ?
3+ messages / 2 participants
[nested] [flat]
* constants in 2-column foreign keys or how to design a storage for text-groups ?
@ 2009-12-09 00:20 Andreas <maps.on@gmx.net>
0 siblings, 1 reply; 3+ messages in thread
From: Andreas @ 2009-12-09 00:20 UTC (permalink / raw)
To: pgsql-sql
Hi,
my frontend has a lot of combo- and listboxes where one can chose a
textsnippet that represents a key-number which is stored in several
tables as foreign-key attributes.
Those textsnippets are usually semantically grouped in 2-10 strings that
belong together somehow.
stupid example:
---------------
color: red, green, blue
size: tiny, little, big, giant
structure: hard, soft, floppy
now I'd like to build tables like
thing( color_fk foreign key to color, size_fk foreign key to size,
structure_fk foreign key to structure, sometext, atimestamp ...)
so far no problems.
With time those little text-list-tables clutter up the database so I'm
thinking about one big text-storage that has the groups represented by a
number like:
snippets (snippet_id, snippet_group_nr, snippet)
(100, 1, red), (101, 1, green), (102, 1, blue), (200, 2, tiny), (201, 2,
little), ...
Simple foreign-keys still work nicely but they cant prohibit that I
store id-values from wrong groups.
Here color_fk would only be correct if the id is out of group 1. The
foreign key doesnt catch it if I put a group-3-id into color_fk.
Id be cool to be able to have constants in 2-column foreign keys like
color_fk integer not null default 0
FOREIGN KEY (color_fk, 1 ) REFERENCES snippets (snippet_id,
snippet_group_nr)
This throws an error. So this approach might be not advisable.
I could add an additional column for every foreign-key that stores
constant group-ids then I can have 2-column-fk but this looks bloated
since those extra columns would hold eternally the same number in every row.
How would I solve the rather common text storage issue?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: constants in 2-column foreign keys or how to design a storage for text-groups ?
@ 2009-12-11 08:39 Jasen Betts <jasen@xnet.co.nz>
parent: Andreas <maps.on@gmx.net>
0 siblings, 1 reply; 3+ messages in thread
From: Jasen Betts @ 2009-12-11 08:39 UTC (permalink / raw)
To: pgsql-sql
On 2009-12-09, Andreas <maps.on@gmx.net> wrote:
> Hi,
...
> stupid example:
> ---------------
> color: red, green, blue
> size: tiny, little, big, giant
> structure: hard, soft, floppy
>
> How would I solve the rather common text storage issue?
have you considered using enumerated types instead?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Re: constants in 2-column foreign keys or how to design a storage for text-groups ?
@ 2009-12-11 10:20 Andreas <maps.on@gmx.net>
parent: Jasen Betts <jasen@xnet.co.nz>
0 siblings, 0 replies; 3+ messages in thread
From: Andreas @ 2009-12-11 10:20 UTC (permalink / raw)
To: pgsql-sql
Jasen Betts schrieb:
> On 2009-12-09, Andreas <maps.on@gmx.net> wrote:
> ...
>
>> stupid example:
>> ---------------
>> color: red, green, blue
>> size: tiny, little, big, giant
>> structure: hard, soft, floppy
>> How would I solve the rather common text storage issue?
>>
>
> have you considered using enumerated types instead?
Yes, but I need those texts in the GUI to show them as listboxes or
comboboxes.
There might be changes too when I later need to add or drop an option of
a group.
E.g. there are questionnaires to model.
Lets say 10 questions where each has a couple of predefined answers
where one should be selected.
So I've got to store every group of possible answers to a question
either in a seperate table or in a kind of repository all within one big
table (row_id, questionnare_id, question_id, answer_nr, answer)
The row_id so I just need to store 1 value per answer.
Now I've got to make sure that it is impossible that accidentally there
gets a question-17 answer connected to a question-42 and above all, that
the questions dont get mixed between the questionnaires.
I can do this with the frontend and enough hope that nothing bad will
happen.
Still I'd rather hardwire the integrity into the table design.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2009-12-11 10:20 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-12-09 00:20 constants in 2-column foreign keys or how to design a storage for text-groups ? Andreas <maps.on@gmx.net>
2009-12-11 08:39 ` Jasen Betts <jasen@xnet.co.nz>
2009-12-11 10:20 ` Andreas <maps.on@gmx.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox