[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-sql
Subject:    [SQL] constants in 2-column foreign keys or how to design a storage for
From:       Andreas <maps.on () gmx ! net>
Date:       2009-12-09 0:20:50
Message-ID: 4B1EED62.6050600 () gmx ! net
[Download RAW message or body]

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?



-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[prev in list] [next in list] [prev in thread] [next in thread]