Re: [PHP-DB] table relationship
Yes. You have to use the subtype relational design: USER user_id (PK) | | | || | || AdministratorClient 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 [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, March 10, 2003 10:35 AM Subject: [PHP-DB] 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) | | | || | || AdministratorClient 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
Re: [PHP-DB] table relationship
Three tables seems wasteful, and could mean that you have to do three queries when looking for someone. Why not approach it this way. Users table - info on all users, regardless of category Levels table - sets different access levels, e.g. clients, staff, administrators User_levels table - assigns levels to user id's This would give you more long-term flexibility as you would only have to extend the levels table to add granularity of access or control levels. I'd also have a look at various network permission schemes, because there are subtleties that are not immediately apparent when working up an access scheme. Cheers - Miles Thompson At 09:35 AM 3/10/2003 +, shaun 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) | | | || | || AdministratorClient 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
