Database design with 3 kinds of user
Posted: Mon Aug 15, 2011 1:46 am
Hi,
I have a website with 3 different types of accounts,
1. Super-Admin (Me, all privileges for website)
2. Admin (Employees for my company, who can verify/accept/delete/modify user accounts)
3. Users (Sign up and put up photos, personal particulars, hobbies etc.)
Users are searchable, I can search for any users and browse their profiles.
Admins are not searchable, and should not show up on any search results
Super-Admins are not searchable either.
How should I design the database in this scenario, should I use 1, 2, or 3 tables to stored the 3 different account types?
*EDIT* If I use 1 table, I will need 1 field to specify the privilege level. However, that means when anyone does a search on my search page for users, the script will always have to go through one round of selection first, like so:
SELECT * FROM users WHERE privilege_level = 1;
running through 50,000 records every time before the actual search even begins, which seems kind of redundant to me.
If I use 2 tables, I can store super-admin and admin in the 'staff' table, and the other users in the 'users' table, however my login script will then look bulky, like so:
$query = mysql_query("SELECT * FROM staff WHERE username = entered_username AND password = entered_password");
if (mysql_num_rows($query) == 0)
$query = mysql_query("SELECT * FROM users WHERE username = entered_username AND password = entered_password");
The account creation script will be a messy one too because I need to check both tables for duplicates, or a user could possibly login to a staff account if he incidentally had the same login/password as the staff.
I also considered the possibility of having separate login script for staff and user, so that I only check against their respective databases only. This sounds good actually, but then I'd need a staff login page, and I rare see any staff login links on websites. Unless of course these links are given to staff only, feels a little off though, or maybe I'm just new to such a concept.
Anyone knows a good way to go about this?
Thanks a lot!
I have a website with 3 different types of accounts,
1. Super-Admin (Me, all privileges for website)
2. Admin (Employees for my company, who can verify/accept/delete/modify user accounts)
3. Users (Sign up and put up photos, personal particulars, hobbies etc.)
Users are searchable, I can search for any users and browse their profiles.
Admins are not searchable, and should not show up on any search results
Super-Admins are not searchable either.
How should I design the database in this scenario, should I use 1, 2, or 3 tables to stored the 3 different account types?
*EDIT* If I use 1 table, I will need 1 field to specify the privilege level. However, that means when anyone does a search on my search page for users, the script will always have to go through one round of selection first, like so:
SELECT * FROM users WHERE privilege_level = 1;
running through 50,000 records every time before the actual search even begins, which seems kind of redundant to me.
If I use 2 tables, I can store super-admin and admin in the 'staff' table, and the other users in the 'users' table, however my login script will then look bulky, like so:
$query = mysql_query("SELECT * FROM staff WHERE username = entered_username AND password = entered_password");
if (mysql_num_rows($query) == 0)
$query = mysql_query("SELECT * FROM users WHERE username = entered_username AND password = entered_password");
The account creation script will be a messy one too because I need to check both tables for duplicates, or a user could possibly login to a staff account if he incidentally had the same login/password as the staff.
I also considered the possibility of having separate login script for staff and user, so that I only check against their respective databases only. This sounds good actually, but then I'd need a staff login page, and I rare see any staff login links on websites. Unless of course these links are given to staff only, feels a little off though, or maybe I'm just new to such a concept.
Anyone knows a good way to go about this?
Thanks a lot!