CODEWITHSUNDEEP
mysqllaraveleloquent
Marco Santos
Marco Santos

Reputation: 1005

Relational tables best practice

im building a application where will run a mysql database, and in the database i will have some relational tables, but latelly i been looking different relational tables online different of how im used to do, basically i dont no what is the best practise and hope in finding the best way to go, above i leave a small example of how i normally do and other online examples:

My practice:

users
- id
- role_id;
- email
- password


roles:
- id
- title

Online Example from others

users
- id
- email
- password

role_user:
- role_id
- user_id

roles:
- id
- title

Basically my question is wich one is better, in terms of best practise and scalability?

Upvotes: 1

Views: 93

Answers (3)

abhishek.agarwal
abhishek.agarwal

Reputation: 36

In Simple words, if you think that one user gonna have multiple roles, then 'role_id' column of 'users' table in become insufficient, so, if are you pretty sure that one user will have only one role anyhow, then first one is fine

else go with second one!

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133370

the second is normally used when you need a separated module for rbac functionality .. in this way the aspcted related to the role are not intrinsecally related to the user authentication module..

Your solution is formally correct butn don't keep in the right consideration the design aspctec of keep the modules separated ..

Upvotes: 1

Christopher Davies
Christopher Davies

Reputation: 4551

It depends on if you want a many-to-many or a one-to-many relationship. In your first example, that's a one-to-many relationship. In other words, a user can have at most one role. In the second example, users can have many roles and roles can apply to many users.

So, if you need users to be in more than one role, use the second example. Otherwise, your first example is just fine.

Upvotes: 3

Related Questions