From: Andreas <maps.on@gmx.net>
To: PostgresSQL list <pgsql-sql@postgresql.org>
Subject: constants in 2-column foreign keys or how to design a storage for text-groups ?
Date: Wed, 09 Dec 2009 01:20:50 +0100
Message-ID: <4B1EED62.6050600@gmx.net> (raw)
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?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: maps.on@gmx.net
Subject: Re: constants in 2-column foreign keys or how to design a storage for text-groups ?
In-Reply-To: <4B1EED62.6050600@gmx.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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