List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:January 2 2004 9:48am
Subject:Re: Multiple Roles
View as plain text  
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

Thread
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