users | roles tables structure question ?

By qqboy on 24 Jul 2015 at 02:35 UTC

We see there is 3 tables

users
role
users_roles

May I ask where there is 3 table, but why not 2 tables, and put uid in role table, and rid in users table ?

Can some one make it clear for us. Thanks.

May i know what reason behind this design structure?
May I know how many records for a normally-designed database table can be called a 'big data', or how many bytes of data can be called 'big data' which result a 'slow query'?

Thanks.

Comments

Users defines user. Ex:

Jaypan commented 24 July 2015 at 03:16

Users defines user. Ex:
* Jaypan (uid 1)
* qqboy (uid 2)

Roles defines roles. Ex:
* admin (rid 3)
* moderator (rid 4)

users_roles connects the two. Ex:
UID | RID
1 | 3 (Jaypan is an admin)
1 | 4 (Jaypan is a moderator)
2 | 4 (qqboy is a moderator)

This structure allows for multiple users to have multiple roles. It's called a many-to-many relationships.

May I ask where there is 3 table, but why not 2 tables, and put uid in role table

If the uid was in the role table, only one UID could be given for each role. This means only one person could be assigned to each role.

and rid in users table ?

If you put rid in users table, it means each user could only be assigned one role. This means that users could not have multiple roles.

If the uid was in the role

qqboy commented 24 July 2015 at 03:35

If the uid was in the role table, only one UID could be given for each role. This means only one person could be assigned to each role.

Not exactly, for example, you can put rid of 1 and 2 to one user by rid 1-2-58-12 what ever what as you like.
What do you think ?

https://www.drupal.org/project/image_captcha_indicator
https://www.drupal.org/project/ext_js

You mean multiple values in a

Jaypan commented 24 July 2015 at 03:42

You mean multiple values in a single column?

yes

qqboy commented 24 July 2015 at 04:04

yes
then
is that any problem ?

https://www.drupal.org/project/image_captcha_indicator
https://www.drupal.org/project/ext_js

All sorts of problems. For

Jaypan commented 24 July 2015 at 06:03

All sorts of problems. For example, if I want to remove the admin role from Jaypan, I would use:

DELETE FROM {users_roles} WHERE uid = 1 AND rid = 3

How would you do that if multiple values were packed in the same column? I'm not even sure it could be done in a single query.

And what about selecting all users of a role? This wouldn't work:

SELECT uid FROM {users} WHERE role LIKE "%3%"

because you'd get users with role 3, 33, 333 etc.
And this wouldn't work:
SELECT uid FROM {users} WHERE role LIKE "%-3-%" because if your column looked like 3-4-5, it would not find it.

Also, you couldn't index the column on the role. So even if you found a query that allowed you to select all users of a given role, you couldn't index the column properly to ensure that query worked quickly.

There are many other issues too. It's a bad practice to store multiple values in a single column.

Hopefully that clears it up for you!