Re: Tabe Relationship

On Mon, 10 Mar 2003 18:00:49 +0100, Damjan S. Vujnović wrote:

> : I wouldn't consider it bad practice to do as outlined below. I would,
> : however, add an indication in the USER table as to what Type of user
> : it is -- an Administrator, a Client, or Staff. Without such a Type
> : field,
> : you're gonna have to check all 3 tables to see which it's in. The Type
> : will point it at a specific table.
>
> What if USER can be both Administrator and Staff (or any other combination)?
>

Hmm, I must confess I didnt think of that. However, the following might be a solution:

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, user_type),
	check (usertype between 1 and 3)

create table administrator (

	admin_id integer not null,
	user_type integer not null default 1,
...
	foreign key (admin_id, user_type) 
		references user (user_id, user_type),
	check (usertype = 1)

...

/Lennart

> Regards,
> Damjan S. Vujnovic
>
> University of Belgrade
> School of Electrical Engineering
> Department of Computer Engineering & Informatics
> Belgrade, Serbia
>
> http://galeb.etf.bg.ac.yu/~damjan/
Received on Mon Mar 10 2003 - 16:41:08 CST