[sqlite] Defining user groups in SQLite table
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! -- View this message in context: http://www.nabble.com/Defining-user-groups-in-SQLite-table-tp25281126p25281126.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Defining user groups in SQLite table
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
Re: [sqlite] Defining user groups in SQLite table
table groups ( group_id, group_descri ) table access ( rela_group, rela_code) --- El jue 3-sep-09, karenebs escribió: > De: karenebs > Asunto: [sqlite] Defining user groups in SQLite table > Para: sqlite-users@sqlite.org > Fecha: jueves, 3 de septiembre de 2009, 2:57 pm > > 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! > -- > View this message in context: > http://www.nabble.com/Defining-user-groups-in-SQLite-table-tp25281126p25281126.html > Sent from the SQLite mailing list archive at Nabble.com. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > Yahoo! Cocina Encontra las mejores recetas con Yahoo! Cocina. http://ar.mujer.yahoo.com/cocina/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Defining user groups in SQLite table
On Thu, Sep 03, 2009 at 10:57:13AM -0700, 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. Thanks in advance for any suggestions! Group memberships are best modelled as separate objects. You need three tables then: one for users, one for groups, and one for group memberships: CREATE TABLE users (userid INTEGER PRIMARY KEY, username TEXT, ...); CREATE TABLE groups (groupid INTEGER PRIMARY KEY, groupname TEXT, ...); CREATE TABLE group_memberships (groupid INTEGER, userid INTEGER, UNIQUE (groupid, userid)); You could sprinkle FOREIGN KEY and add ON CONFLICT IGNORE: CREATE TABLE group_memberships (groupid INTEGER, userid INTEGER, UNIQUE (groupid, userid) ON CONFLICT IGNORE, FOREIGN KEY (groupid) REFERENCES groups (groupid), FOREIGN KEY (userid) REFERENCES users (userid)); You could even sprinkle ON DELETE/UPDATE/INSERT clauses: CREATE TABLE group_memberships (groupid INTEGER, userid INTEGER, UNIQUE (groupid, userid) ON CONFLICT IGNORE, FOREIGN KEY (groupid) REFERENCES groups (groupid) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (userid) REFERENCES users (userid) ON DELETE CASCADE ON UPDATE CASCADE); This makes queries for memberships and mebers, both very fast. And you can always use group_concat() to get a single string with all groups that a user is a member of or all users that are members of a group. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Defining user groups in SQLite table
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
