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?