On 3 Sep 2009, at 6:57pm, karenebs wrote: > I have a database table that holds about 20,000 codes. Each code > can be used > by several different user groups. I could add a column to the > database > table for each user group to indicate which codes that particular > group has > access to. But, I have over 100 different groups! Can anyone suggest > another way of doing this? So, group 1 has access to code1, code2, > code3, > code4, code5; group 2 has access to code1, code2, code3; group 3 has > access to code 2, code3, code4. etc.
It's a classic many-to-many relational model. So the classic way to do it is to create another table listing your groups, and to have a many-to-many table between the two of them, showing which groups can use which codes. If the only thing you care about groups is the group names, and there's no point in keeping any more information than that about your groups, you don't have to do it like that. You can instead add another column to your 'codes' table which is just a long string that lists all the groups that can use that code: group1 group4 group7 group21 Alternatively you can create a groups table, and in that table list all the codes that group has access to: group7 code3 code4 code5 code30 code31 Which of these three things you do depends on which is the most convenient and useful for your purposes. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
