MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Lin Yu Date:July 28 2003 6:33pm
Subject:RE: Design decision
View as plain text  
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: csebe@stripped [mailto:csebe@stripped] 
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/mysql?unsub=1

Thread
importing Access databasesRobert Morgan27 Jul
  • Re: importing Access databasesSanya Shaik27 Jul
  • Re: importing Access databasesJim McAtee27 Jul
  • Re: importing Access databasesSpamBox28 Jul
    • RE: importing Access databaseselectroteque28 Jul
      • Re: importing Access databasesBob Ramsey28 Jul
      • Design decisioncsebe28 Jul
        • RE: Design decisionLin Yu28 Jul
          • RE: Design decisioncsebe28 Jul
    • Re: importing Access databasesAndreas28 Jul