Hi, > > 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 > > > > 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 > > ); > > > > for the second option, you can use VARCHAR for roles_names, only make sure > that you have enough space to define there all combinations of roles. you > can also use BLOB for it (VARCHAR is up to 255 chars length). > > CREATE TABLE user_roles ( > user_name VARCHAR(15) NOT NULL, > roles_names VARCHAR(31) NOT NULL > ); > > roles_names is of length 31 cuz the comma is also a char.
I would advise against this one. First of all: it breaks normal table design. Second: if you add more roles, you need to adjust your metadata (because of (1)). Third: you will run into problems when doing queries. Having a compound primary key which has multiple rows in the table for each role is the normal design. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
