Re: Tabe Relationship

: 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)

First, I suggest you to use DDL when posting to c.d.t. Second, your basic idea is good, but since user_id is the primary key in the USER relation, it should be the primary key in Administrator, Client and Staff relations as well. Or, in DDL:

CREATE TABLE User (

    user_id INTEGER NOT NULL PRIMARY KEY,     ...)

CREATE TABLE Administrator (

    user_id INTEGER NOT NULL PRIMARY KEY,     ...
    CONSTRAINT fk_a_u FOREIGN KEY user_id REFERENCES User(user_id))

CREATE TABLE Client (

    user_id INTEGER NOT NULL PRIMARY KEY,     ...
    CONSTRAINT fk_c_u FOREIGN KEY user_id REFERENCES User(user_id))

CREATE TABLE Staff (

    user_id INTEGER NOT NULL PRIMARY KEY,     ...
    CONSTRAINT fk_s_u FOREIGN KEY user_id REFERENCES User(user_id))

This approach will keep your indices (indexes?) simpler and it will have positive effects on further modelling. The side effect is that it will reduce the overhead while generating the values of (surrogate) primary keys. I suggest you to find a book about ER model(ing). Also, keep in mind that Google is your friend.

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 - 11:47:56 CET