List:General Discussion« Previous MessageNext Message »
From:Roger Baklund Date:January 2 2004 2:47pm
Subject:Re: Multiple Roles
View as plain text  
* Caroline Jen
> In case that a user has multiple roles; for example,
> John Dole is both author and editor,
>
> 1. I should have two rows for John Dole?
>
>    John Dole     author
>    John Dole     editor
>
>    or. I should have only one row and use comma ',' to
>
>    separate the roles?
>
>    John Dole     author, editor

I see you allready got some relevant replies, I just wanted to add some
comments and advise on normalization, which seems to be the core of this
question.

You should _never_ separate data with comma in a column. This violates the
first normal form, called 1NF, which states that a column should contain a
single value of the same type for each row.

You should have _one_ row for John Dole in the users table, and two
corresponding rows in a roles table. In addition you need a table to hold
the combinations.

> 2. How do I create the table for the second case (see
> below)?
>
>   create table user_roles (
>   user_name     varchar(15) not null,
>   role_name     varchar(15) not null, varchar(15) null
>   );

To normalize this fully, you need three tables:

CREATE TABLE users (
  uid int unsigned not null primary key auto_increment,
  name varchar(30) not null,
  unique(name)
);
CREATE TABLE roles (
  rid int unsigned not null primary key auto_increment,
  role varchar(30) not null,
  unique(role)
);
CREATE TABLE user_roles (
  uid int unsigned not null,
  rid int unsigned not null,
  primary key (uid,rid),
  unique (rid,uid)
);

The primary keys are made as small/compact as possible, in this case 4 bytes
for users and roles, and 8 bytes for the combination. You could make this
even more compact, for instance using TINYINT for the rid column if you
don't have more than 250 roles and SMALLINT for uid if you have less than
65000 users. This does not matter much for small tables, but when your data
is considerably larger than the
computer memory, these things become important.

Note that if you need to change the spelling of an existing name or role,
you just have to change it one place, in the 'users' or 'roles' table. The
key (uid/rid) is unchanged, thus the rows in user_roles does not need to
change.

Also note that the data that consumes space (the VARCHAR columns) are stored
only once for each value, and a smaller column, a 4 byte INTEGER is used as
"the key", representing the value stored in the VARCHAR. Now, if you had
50.000 users with an average of 100 roles each, that would be 5M rows in
your user_roles table. With a non-normalized approach, you would store
avg(length(name)) + avg(length(role)) bytes for each row, say 15 + 10 = 25
bytes => 125MB. With the normalized approach suggested above you store only
8 bytes for each row => 40MB in total, compacting further using TINYINT and
SMALLINT you would store only 15MB.

Inserting test data:

INSERT INTO users SET name = 'John Dole';
SET @uid:=LAST_INSERT_ID();
INSERT INTO roles SET role = 'author';
INSERT INTO user_roles SET uid=@uid,rid=LAST_INSERT_ID();
INSERT INTO roles SET role = 'editor';
INSERT INTO user_roles SET uid=@uid,rid=LAST_INSERT_ID();

Now the tables looks like this:

mysql> select * from users;
+-----+-----------+
| uid | name      |
+-----+-----------+
|   1 | John Dole |
+-----+-----------+
1 row in set (0.01 sec)

mysql> select * from roles;
+-----+--------+
| rid | role   |
+-----+--------+
|   1 | author |
|   2 | editor |
+-----+--------+
2 rows in set (0.01 sec)

mysql> select * from user_roles;
+-----+-----+
| uid | rid |
+-----+-----+
|   1 |   1 |
|   1 |   2 |
+-----+-----+
2 rows in set (0.00 sec)

To select all roles for a user:

SELECT role FROM roles
  NATURAL JOIN user_roles
  NATURAL JOIN users
  WHERE name = 'John Dole'

To select all users of a role:

SELECT name FROM users
  NATURAL JOIN user_roles
  NATURAL JOIN roles
  WHERE role = 'editor'

To insert a user/role combination:

1. Get the key for the name:
   SELECT uid FROM users WHERE name = '$name'

2. If the name did not exist, create it:
   INSERT INTO users SET name = '$name';
   Get the key:
   SELECT LAST_INSERT_ID()

3. Get the key for the role:
   SELECT rid FROM roles WHERE role = '$role'

4. If the role did not exist, create it:
   INSERT INTO roles SET role = '$role';
   Get the key:
   SELECT LAST_INSERT_ID()

5. Insert the user_roles row:
   INSERT user_roles SET uid=$uid,rid=$rid;

If the final INSERT fails, the user/role combination allready existed. If
any of the other INSERTs fails you have a "collision": two users are
creating the same user or role at the same time. In that case you should
redo the previous SELECT (step 1 or 3), or take the easy way out and just
restart from step 1.

--
Roger

Thread
Multiple RolesCaroline Jen2 Jan
  • Re: Multiple RolesEli Hen2 Jan
  • Re: Multiple RolesMartijn Tonies2 Jan
  • Re: Multiple RolesTobias Asplund2 Jan
  • Re: Multiple RolesEd Leafe2 Jan
  • Re: Multiple RolesRoger Baklund2 Jan