Relational tables best practice


Problem :

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?



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.


Mysql Tutorials

Mysql Howto..

How does privilege users work on MySQL?

how to compare a string with strings in linkedlist and insert the result as boolean into mysql table

How to select a random row from table1 through a pivot table on MYSQL

How can i update my table on server when i add new records in Mysql query browser on my locall pc?

How to get the distinct of multiple fields in MySQL?

How check annual date condition

How to pivot a query in MySql

How to search for particular strings between commas in MySQL?

How to join three degrees in mySQL

How do I save the output of this code into a file? (PHP) [closed]

How do I control retrieving data from tables in a database

How to store more than 255 char in MySQL database?

MySQL how to select all rows by specific day (mon, tue, etc) with datetime format

mysql show Count of rows from other table in each row

how to prevent insert duplication data in MySQL

How do I add millions of rows to a live production mysql table?

How to count number of fields by specific column in Mysql?

php/mysql - using union, how to set variable based on selected table?

mysql query: how to select distinct based on time

How to query wp_usermeta using WordPress inbuilt functions efficiently?

How do I take SELECTed output and write it to a new TABLE in MySQL? [duplicate]

How to select couple of records for editing in MySql database

How to adjust display settings of mysql command line?

How to associate Article with multiple categories

How to retrive imageid and insert it into another table

How to create table of tables c# my sql

How to specify null date in a query for MYSQL?

In MySql how do I add missing decimals to the currency column?

How to check if website is up and store results for long time period

How to prevent SQL injection? [duplicate]