How: constants in multi-column-foreign-keys ?

From: Andreas <maps(dot)on(at)gmx(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: How: constants in multi-column-foreign-keys ?
Date: 2009-12-10 11:05:58
Message-ID: 4B20D616.7060503@gmx.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,
a lot of attributes get stored as numbers.
Usually I built a small table with a numerical key-column and a
text-column to use in joins.

Is there a way to store them all in a new table that has a 3rd column
for group-numbers instead of having a single table for every
textattribute-group?
It could be like this:
(option_id integer primary key, option_group_id integer, option text)
Since there are never more than 100 options within the groups, I'd
construct the option_ids as group_id * 100 + nr.
table textoptions (100, 1, red), (101, 1, green), (102, 1, blue),
(200, 2, horse), (201, 2, dog), (202, 2, cat), (203, 2, goldfish),
(300, 3, car), (301, 3, boat), ...

In datatables there were foreign-keys like
...
animal_fk integer not null
CONSTRAINT animal_fkey FOREIGN KEY (animal_fk, 2)
REFERENCES textoptions (option_id, option_group_id)
...
This throws an error because of the constant 2.

On the other hand I could add another column and have:
...
animal_group_fk integer not null default 2,
animal_fk integer not null
CONSTRAINT animal_fkey FOREIGN KEY (animal_fk, animal_group_fk)
REFERENCES textoptions (option_id, option_group_id)
...
This gets accepted, though it bloats up the tables because I need 1
additional group-column that never gets changed for every foreign-key I
need.

Maybe I'm all very wrong with my approach to the text-option store.
This must be a common issue, though.
Do you know a better way to do it?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message A. Kretschmer 2009-12-10 11:40:39 Re: How: constants in multi-column-foreign-keys ?
Previous Message Dimitri Fontaine 2009-12-10 10:44:08 Re: [HACKERS] Installing PL/pgSQL by default