Vote Up 0 Vote Down

php - One or more table

How best to store users - in one table it will be and Clint, and staff, and administrators? Or separately for each? It's still under authorization will need to check first on the same table, if there is such a user, if not, check to another.
flag

Answers


Vote Up 0 Vote Down
Ideally, for these purposes you must have at least two, and four tables, and optimally completely with other logic.

If you have one table, then there is no separation of logic is not, in principle, and the table has a right to exist, but it is inconvenient to use in the future, if you plan to empowerment, is problematic.

Your version with multiple tables is not suitable. Just imagine what would happen if the user is changing the role, you have added a new role (for example, the corrector) or it should have more than one role.

Optimal option

The first table is the main and contains identification data, call it users:

users -- id -- login -- password

This is the data that you will be more likely to apply when checking for input.

The second table contains the user's role (user, admin, moderator, correction, etc):

role -- id -- role

The third table connects them:

permission -- id -- users.id -- role.id

Now you can create a query that will select the role (or roles) of these tables.

I also mentioned a fourth table, it is better to store information about the user. Just because this data you will not often treated as a table users, and it is desirable to allocate a separate table.



From which it is already possible to select the desired data query:

SELECT r.role FROM role r JOIN permission p ON r.id = p.role_id JOIN users u ON u.id = p.user_id WHERE u.login = 'Alex'

PS Despite the apparent complexity of the structure, this model has many advantages (control change data, the separation of logical structures, easy refactoring, adding new features) and lacks many of the disadvantages of using a single table. If you want to know more about the benefits, study NF and that they give.
flag | link |
Vote Up 0 Vote Down
You need to create two tables (or three). One role of users in other users themselves with reference to the role of ID, (one to one). If one user is assigned to multiple roles, plant of the third staging table with attitude ID, user ID, to the role (one to many).
flag | link |

Your Answer

Login before answering

Login with facebook