On Thu, Sep 03, 2009 at 10:57:13AM -0700, karenebs scratched on the wall:
>
> 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. Thanks in advance for any suggestions!
This is a classic Many-to-Many relationship. You're basically
putting "people" (codes) into "groups". People can belong to more
than one group, groups have more than one person.
The standard way of doing this is to have a GROUPID in your Groups
table and a CODEID in your Codes table. You then add a third table,
known as a "bridge" or "link" table, that represents a membership.
It has at least two columns: GROUPID and CODEID. Put a unique index
across both columns and you're good to go (you may want a second
index with the columns in the other order). To add a code to a
group, just add a new membership with the correct ID values. To
query, join your codes and groups across the bridge table.
You can add other columns to the link table if you want, such as a
starting date or ending date for the membership, a membership type,
etc.
Depending on your needs, you many need to search and cleanup
codes with zero groups or groups with zero codes.
You are likely to get all kinds of examples if you Google "sql many
to many" or "sql bridge table".
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Our opponent is an alien starship packed with atomic bombs. We have
a protractor." "I'll go home and see if I can scrounge up a ruler
and a piece of string." --from Anathem by Neal Stephenson
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
