Database design with 3 kinds of user

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
siko
Forum Commoner
Posts: 37
Joined: Tue Feb 16, 2010 11:28 pm

Database design with 3 kinds of user

Post by siko »

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!
Last edited by siko on Mon Aug 15, 2011 11:16 am, edited 2 times in total.
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Database design with 3 kinds of user

Post by social_experiment »

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.
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?

Code: Select all

<?php
 // example
 SELECT * FROM tableForUsers
?>
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.
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.
“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
siko
Forum Commoner
Posts: 37
Joined: Tue Feb 16, 2010 11:28 pm

Re: Database design with 3 kinds of user

Post by siko »

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! :)
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Database design with 3 kinds of user

Post by social_experiment »

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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Database design with 3 kinds of user

Post by califdon »

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.
User avatar
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

Post by Christopher »

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)
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Database design with 3 kinds of user

Post by VladSun »

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 :)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
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

Post by Christopher »

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)
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Database design with 3 kinds of user

Post by VladSun »

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.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply