On Jan 2, 2004, at 4:28 AM, Caroline Jen wrote:
> 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
> 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