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
