List:General Discussion« Previous MessageNext Message »
From:Ed Leafe Date:January 2 2004 2:31pm
Subject:Re: Multiple Roles
View as plain text  
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:

Multiple RolesCaroline Jen2 Jan
  • Re: Multiple RolesEli Hen2 Jan
  • Re: Multiple RolesMartijn Tonies2 Jan
  • Re: Multiple RolesTobias Asplund2 Jan
  • Re: Multiple RolesEd Leafe2 Jan
  • Re: Multiple RolesRoger Baklund2 Jan