Control different access levels

Question

Hello all,

I plan to use two tables (i.e. guest and employee) to control which page is available to which user.

The guess can sign up for an account while the employee cannot. The account of an employee can only be assigned by a system administrator.

Here is the question I just found:

Assume UserA is a employee and has 'login001' as user name and 'password' as password.

Now, a guess comes to the website and applies for a user name with 'login001' and 'password' as password.

Then the userB can access some internal websites b/c he/she chose the same username/password as one of the employee does.

Here is my fix to this problem:

Method one: Whenever a user name is applied (not matter guess or employee), the both table guess and employee should be checked to make sure there is no duplicate.

Method two: Combine guess and employee tables together as person table. However, for guess, the user_type can be assigned as GUEST and for employee, the user_type can be assigned as EMPLOYEE.

I don't know whether above methods make sense or not. If neither or them are good solutions, please give me some direction so that I can adopt some best practice.

I am NOT using any framework or OOP to implement my PHP script.

Thank you

Answer 1

You should probably put all the logins together in one table since they're going to share a lot of the same columns. Those duplicated columns aren't an ideal way to atomize your data.

Use method two, it will minimize your headaches later and the data is much easier to understand.

Answer 2

I would recommend you combine your two credential tables into 1. Then you can create a guest and employee table with a reference to the credential table. This way you can store additional information about the two.

However, you may want to ask yourself what the difference between an guest and an employee is. If the only difference is access control, you may be able to get away with an additional column in the credential table that defines whether the record is a guest record or an employee record.

Finally, once you have combined your credentials into 1 table, you should put a unique constraint on your username column. This will ensure that only 1 account can have a username like login001 or joeshmoe.