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]
