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/[EMAIL PROTECTED]
