Re: Tabe Relationship

On Mon, 10 Mar 2003 09:29:48 +0000, john wrote:

> Hi,
>
> I am creating a web site which will have different types of users:
> Administrators, clients and staff. Is it possible/good practice to have 3
> tables related to one table i.e.
>
>
> USER
> user_id (PK)
> |
> ____________|____________
> | | |
> | | |
> Administrator Client Staff
> admin_id(PK) client_id(PK) staff_id(PK)
> user_id(FK) user_id(FK) user_id(FK)
>
>
> Thanks in advance for any advice offered.

It is possible, but if Administrator, Client and Staff have the same set of attributes, I would keep them in the same table together with an usertype attribute. I think it is also advisible have the usertype attribute in case of super/subtyping. Simple example:

create table user (

	user_id	integer not null,
	user_type integer not null,
	user_name varchar(20) not null, -- common to all subtypes
	primary key user_id,
	unique (user_id, user_type),
	check (usertype between 1 and 3)

create table administrator (

	admin_id integer not null,
	user_type integer not null default 1,
	admin_attrib_1  <type>,  -- specific for administrators
	...
	admin_attrib_n  <type>,  -- specific for administrators
	primary key admin_id,
	foreign key (admin_id, user_type) 
		references user (user_id, user_type),
	check (usertype = 1)

create table client (

	client_id integer not null,
	user_type integer not null default 2,
	client_attrib_1  <type>,  -- specific for clients
	...
	client_attrib_m  <type>,  -- specific for clients
	primary key client_id,
	foreign key (client_id, user_type) 
		references user (user_id, user_type),
	check (usertype = 2)

...

/Lennart Received on Mon Mar 10 2003 - 13:56:50 CET