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 );
Both are poor solutions. You should have a person table and a role table, and join them using a third (typically called an allocation or assignment table, or simply a many-to-many table).
This third table contains only the PKs of the person and their role. Typically it has only three columns (its own PK, person_FK and role_FK), but can optionally have additional columns if additional information about the relationship is needed.
It is then a matter of joining the person table to the role table through the allocation table to get a list of all roles for a given person. Reversing the queries then gives you all people who have a given role.
___/ / __/ / ____/ Ed Leafe
Linux Love: unzip;strip;touch;finger;mount;fsck;more;yes;umount;sleep
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
