constants in 2-column foreign keys or how to design a storage for text-groups ?
От | Andreas |
---|---|
Тема | constants in 2-column foreign keys or how to design a storage for text-groups ? |
Дата | |
Msg-id | 4B1EED62.6050600@gmx.net обсуждение исходный текст |
Список | 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?
В списке pgsql-sql по дате отправления: