Thread: constants in 2-column foreign keys or how to design a storage for text-groups ?
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?
Re: constants in 2-column foreign keys or how to design a storage for text-groups ?
From
Jasen Betts
Date:
On 2009-12-09, Andreas <maps.on@gmx.net> wrote: > Hi, ... > stupid example: > --------------- > color: red, green, blue > size: tiny, little, big, giant > structure: hard, soft, floppy > > How would I solve the rather common text storage issue? have you considered using enumerated types instead?
Re: Re: constants in 2-column foreign keys or how to design a storage for text-groups ?
From
Andreas
Date:
Jasen Betts schrieb: > On 2009-12-09, Andreas <maps.on@gmx.net> wrote: > ... > >> stupid example: >> --------------- >> color: red, green, blue >> size: tiny, little, big, giant >> structure: hard, soft, floppy >> How would I solve the rather common text storage issue? >> > > have you considered using enumerated types instead? Yes, but I need those texts in the GUI to show them as listboxes or comboboxes. There might be changes too when I later need to add or drop an option of a group. E.g. there are questionnaires to model. Lets say 10 questions where each has a couple of predefined answers where one should be selected. So I've got to store every group of possible answers to a question either in a seperate table or in a kind of repository all within one big table (row_id, questionnare_id, question_id, answer_nr, answer) The row_id so I just need to store 1 value per answer. Now I've got to make sure that it is impossible that accidentally there gets a question-17 answer connected to a question-42 and above all, that the questions dont get mixed between the questionnaires. I can do this with the frontend and enough hope that nothing bad will happen. Still I'd rather hardwire the integrity into the table design.