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
