mysqllaraveleloquent

Which of these relational DB table designs is better, in terms of best practice and scalability?

I'm building a application using Laravel and Eloquent running a MySQL database. I have seen relational tables online different from how I'm used to.

My practice:

users (id, role_id, email, password)
roles (id, title)

Online example:

users (id, email, password)
role_user (role_id, user_id)
roles (id, title)

Which is better, in terms of best practice and scalability?

Solution

  • 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.