* 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 [EMAIL PROTECTED],rid=LAST_INSERT_ID(); INSERT INTO roles SET role = 'editor'; INSERT INTO user_roles SET [EMAIL PROTECTED],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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
