I'm building a application where I will run a MySQL database, and in the database I will have some relational tables, but lately I been looking different relational tables online different of how I'm used to do, basically I don't know what is the best practice 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 which one is better, in terms of best practice and scalability?
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.