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
> 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
