Lin, thanks for your input. Indeed I forgot to mention there is a many-to-many relation between users and groups.
I'm inclined though to use Solution 3. My main concern with 2 and 3 was not to exceed the column allocated space for the concatenated string, when it grows with the number of users in a group. I calculated the space requirement for storing a string resulted by 1 milion user IDs each separated by a character. It's taken about 8 milion bytes to store the whole string. A MediumText gives me 16 MB so I think I'm pretty much covered. As I said my main requirements would be speed when a Select is performed. So I prefer to do some additional logic in perl and to retrieve faster results in one Select returning only one row. Hope I'm not wrong ;-) Thanks again, Lian Sebe, M.Sc. Freelance Analyst-Programmer www.programEz.net > -----Original Message----- > From: Lin Yu [mailto:[EMAIL PROTECTED] > Sent: Monday, July 28, 2003 9:33 PM > To: [EMAIL PROTECTED]; 'mysqllist' > Subject: RE: Design decision > > > Lian, > > Between your design solutions (1) and (3), you need to decide, > from the logical > business requirement, whether the nature of the relationship > between user and > group is one-to-many (a group may have many users, and each user > may belong to > exactly one group) or many-to-many (a group may have many users, > and each user > may belong to multiple groups). For the former, use Solution (3), for the > latter, use Solution (1). Granted, Solution (3) is a subset of > Solution (1), but > requires more resources which might be a waste if you only need > represent a > one-to-many relationship. > > Your solution (2) has no restriction on the granularity of the > relationship > i.e., it can support both; it all depends on your implementation > outside SQL, > thus is not really a DB schematic means. In this case, the relationship is > actually interpreted and maintained by your application program, > not by DBMS. > > In making a choice between Solution (2) and the other two you > need to consider > the performance difference and code maintenance. > > Best regards, > ________________________ > Lin > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Monday, July 28, 2003 10:22 AM > To: mysqllist > Subject: 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/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
