Database design with 3 kinds of user
Moderator: General Moderators
Database design with 3 kinds of user
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!
Last edited by siko on Mon Aug 15, 2011 11:16 am, edited 2 times in total.
- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: Database design with 3 kinds of user
If you use 3 tables, and you don't want any tier 2 and 3 (super-admin / admin) staff to be listed, wouldn't it be easier to not search the 2 additional tables?siko wrote: 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.
...
If I use 3 tables, 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.
Code: Select all
<?php
// example
SELECT * FROM tableForUsers
?>Imho, i would store login credentials in a seperate table and after correct login, use a foreign key (something like an id) to tie the two table (login and user_details) together. User_details will contain any user related information.siko wrote: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.
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
Re: Database design with 3 kinds of user
Ah yes, that was my mistake, I meant to say 1 table - if i use 1 table to store everything, I'd need to select the users from the staff for every search.
Gave it some thought, a nice idea! Gonna try that out, thanks!
Gave it some thought, a nice idea! Gonna try that out, thanks!
- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: Database design with 3 kinds of user
If you use one table then the privilege field would be necessary so you add that into your search, similar to what you currently have in mind
Code: Select all
<?php
$qry = "SELECT * FROM userTable WHERE name = '" . $username . "' AND privilege = '1'";
?>“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
Re: Database design with 3 kinds of user
I'll just add that decisions about database structure should be based on the definition of entities and the relationships between entities--and on nothing else! You are likely to have serious trouble if you introduce your concepts of what "seems kind of redundant" or what you imagine is the efficiency of a query, when determining your table structure. 50,000 rows is not at all a large table and unless you have a deep understanding of the internals of a database engine like MySQL (which I don't pretend to have), your guesses about whether a query is efficient are essentially meaningless. Just stick to the rules of relational database design and normalization. From what you have described, it seems to me you are talking about only one entity--users--so that requires one table. Every user has an attribute that defines that user's privileges level, which you would use as part of every query where it is applicable.
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: Database design with 3 kinds of user
Two tables sounds like Premature Optimization to me. Since you don't actually have a performance problem, don't program a solution for a non-problem. I also think you misunderstand how databases do SELECTs. It will optimize the query on whatever values you are searching for -- adding the account type as an additional constraint will probably not have much effect.
(#10850)
Re: Database design with 3 kinds of user
I don't think it's a premature optimization - it's just the proper DB design.
The problem described is a pretty good example for 1:1 none-mandatory relationship. Though having a second table may lead to decreased performance in this case
The problem described is a pretty good example for 1:1 none-mandatory relationship. Though having a second table may lead to decreased performance in this case
There are 10 types of people in this world, those who understand binary and those who don't
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: Database design with 3 kinds of user
It is pre-mature optimization because there is no performance problem -- and probably never will be. Worse it is an example of focusing on the wrong thing when solving the problem. It is the wrong partition of the data. If users have data that must be searched efficiently then it makes much more sense to move that data into a separate table optimized and indexed for searching. That data has nothing to do with authentication credentials or access controls -- unless you allow the public to search for passwords and account types! 
(#10850)
Re: Database design with 3 kinds of user
As I said, moving access levels to a separate table has nothing to do with optimization - it's just the proper RDBM design. If you look at the "one-to-one-none-mandatory-relation" usage cases you will find that this case is often used as an example for such relation.
Moving access levels to a separate table has almost the same meaning as "decoupling", "dependency injection", etc.
Software design and DB design should both be build properly, right? Software developers often miss the importance of the proper DB design in favor of the software design... and it shouldn't be so. It's clear, that not every software developer should/must be a DB admin/designer, and that's why many companies have a standalone "DB designer/admin" position, but SW developers should keep in mind that nowadays DB engines (thus so are DB designs) are very complex and powerful if used properly.
Moving access levels to a separate table has almost the same meaning as "decoupling", "dependency injection", etc.
Software design and DB design should both be build properly, right? Software developers often miss the importance of the proper DB design in favor of the software design... and it shouldn't be so. It's clear, that not every software developer should/must be a DB admin/designer, and that's why many companies have a standalone "DB designer/admin" position, but SW developers should keep in mind that nowadays DB engines (thus so are DB designs) are very complex and powerful if used properly.
There are 10 types of people in this world, those who understand binary and those who don't