I have a dilemma, I have two initial tables; user and car . The car table has a column called cash_car that contains a total of money in whole number. I need to differentiate the total that each user has entered (in this case only two). Any ideas to improve the data model?
If there is a 1: 1 ratio, that is, a user for a car
You can leave the tables as they are.
For the query, you would have to do JOIN of both tables using the column that relates them, in this case id_user .
Example:
SELECT * FROM usuario u
INNER JOIN
carro c ON u.id_user=c.id_user
WHERE u.id_user=1;
That query will bring you all the columns of both user tables 1. You can change the * by the column names you want. I used u and c as alias of each table.
If there is a 1: M or M: M relationship
That is, if a car could be used by several people, or if the same person could use the same car on different dates ...
You will need a third table that manages the relationships. Why? Because that way, every time someone goes to use a car you do not have to repeat all the columns of the car again and again. This avoids data redundancy and many other factors.
Something that helps in the case of tables like usuario , carro ... is to think each row of our table as a single entity, which should never be repeated.
That third table called symptomatically usuario_carro would be more or less like this:
id (autoincremental station) id_user id_carro fecha It would have a unique index combining id_user and id_carro to avoid that the same user can have twice the same car. Or the unique index would combine the columns ( id_user, id_carro, fecha ) to avoid that the same user could have more than once the same car on the same date ... That will depend on the context of your application.
The query would then be like this:
SELECT * FROM usuario_carro uc
INNER JOIN
usuario u ON uc.id_user=u.id_user
INNER JOIN carro c ON uc.id_carro=c.id_carro
WHERE u.id_user=1;