Design decision

Hi everyone,

Just wanted your expert opinion on the following:

I'm implementing an authorization system with user/group permissions stored
in a database. I have a Users table and a Group table, identical in
structure:
mysql> desc users;
mysql> desc groups;
+-------+-------------+
| Field | Type |
+-------+-------------+
| id | int(11) |
| name | varchar(30) |
+-------+-------------+

Now, my question is "How to store BEST the relations between users and
groups?".

Solution 1. I use a separate table with this structure:
mysql> desc users2groups;
+---------+---------+
| Field | Type |
+---------+---------+
| idUser | int(11) |
| idGroup | int(11) |
+---------+---------+
and I add one record for each user <--> group mapping. So a SELECT will
return potentially many rows for one group or one user.

Solution 2. I construct and maintain a string separated by colons (let's
say) for each group. So in the users2groups I'd have for example:
| idGroup | idUser |
| 123 | 2:3:4:8:9:10 |

Similary, since I need also user-to-group lookups I construct a string for
the "group membership of a user" so I can have in the same table:
| idGroup | idUser |
| 123 | 2:3:4:8:9:10 |
| 123:456 | 4 |

Solution 3. Similary to Solution 2 but using the initial tables extended
with one more field to accomodate the membership constructed string like:
+-----------+-------------+
| Field | Type |
+-----------+-------------+
| id | int(11) |
| name | varchar(30) |
| member_of | text |
+-----------+-------------+

In Solution 1 I have multiple rows returned. In solution 2,3 I have only
one.
Solution 1 is scalable however Solution 2,3 can reach (potentially) the
limits of the column specification (unlikely though).

Assuming I'm interested in maximum speed at the authorization moment (and
not at administrative moment), and that I'll have a big number of users and
groups, and I access the database via Perl (so no problem to
construct/deconstruct strings), what do you think is the best solution?

Thank you for your time,

Lian Sebe, M.Sc.
Freelance Analyst-Programmer
www.programEz.net

"I'm not mad. I've been in bad mood for the last 30 years..."
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw