Differentiate 2 users inside another table [closed]
772
I have a dilemma, I have two initial tables; user and car . The car table has a column called cash_carthat contains an integer total of money. I need to differentiate the total that each user has entered (in this case only two). Any idea to improve the data model?
If there is a 1:1 relationship, that is, a user for a car
You can leave the tables as they are.
For the query, you would have to make JOINboth 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 from both user 1's tables. You can change the *to whatever column names you want. I have used uand cas aliases of each table.
Solution 2
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 is going to use a car you don't have to repeat all the columns of the car over and over again. This avoids data redundancy and many other factors.
Something that helps in the case of tables like usuario, carro... is to think of each row of our table as a unique entity, which should never be repeated.
That third table called symptomatically usuario_carrowould look something like this:
id(PK auto increment)
id_user
id_carro
fecha
It would have a unique index combining id_userand id_carroto prevent the same user from having the same cart twice. Or the unique index would combine the columns ( id_user, id_carro, fecha) to prevent the same user from having the same car more than once 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;
Solution 1
If there is a 1:1 relationship, that is, a user for a car
You can leave the tables as they are.
For the query, you would have to make
JOIN
both tables using the column that relates them, in this caseid_user
.Example:
That query will bring you all the columns from both user 1's tables. You can change the
*
to whatever column names you want. I have usedu
andc
as aliases of each table.Solution 2
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 is going to use a car you don't have to repeat all the columns of the car over and over again. This avoids data redundancy and many other factors.
Something that helps in the case of tables like
usuario
,carro
... is to think of each row of our table as a unique entity, which should never be repeated.That third table called symptomatically
usuario_carro
would look something like this:id
(PK auto increment)id_user
id_carro
fecha
It would have a unique index combining
id_user
andid_carro
to prevent the same user from having the same cart twice. Or the unique index would combine the columns (id_user, id_carro, fecha
) to prevent the same user from having the same car more than once on the same date... That will depend on the context of your application.The query would then be like this: