Yes. You have to use the "subtype" relational design:
USER
user_id (PK)
|
____________|____________
| | |
| | |
Administrator Client Staff
user_id(PFK) user_id(PFK) user_id(PFK)
admin_field1 user_field1 staff_field1
admin_field2
.....
With a 1-1 relationship from Administrator to User, 0-1 relationship from
User to Administrator
(PFK : PRIMARY KEY and FOREIGN KEY)
This is particularly useful when the subtypes have different fields, so you
don't want to have only one table with many blanks for fields that do not
relate to the type at hand.
HTH
Ignatius
----- Original Message -----
From: "shaun" <shaun@mania.plus.com>
To: <php-db@lists.php.net>
Sent: Monday, March 10, 2003 10:35 AM
Subject: table relationship
> 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.
>
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
