I have a bit of trouble with this topic. They have asked me to make a relation entity between 3 tables, the users table, the sales table and the news table, a user can create many sales and many news, that is, it will be a relationship of 1 to many between users and the other two tables. The problem I have now is that I don't know very well how to place the foreign key correctly. I need to use the username to place him as the author of news and sales that he has created. But I don't know how to join it if through an id from the users table that refers to sales and news, or should I put the username as a foreign key?
I leave here the tables that I have made:
Tabla usuarios
[PK]+id - bigint(20)
+name - varchar(255)
+surname - varchar(255)
+email - varchar(255)
+created_at - timestamp
+updated_at - timestamp
Tabla ventas
[PK]+id - bigint(20)
+titulo - varchar(255)
+autor - varchar(255) --> esto es lo que me gustaría enlazar con usuarios
+precio - int(11)
+nombre_vendedor - varchar(255)
+contacto_vendedor - varchar(255)
+detail - text
+image - varchar(255)
+created_at - timestamp
+updated_at - timestamp
Tabla noticias
[PK]+id - bigint(20)
+titulo - varchar(255)
+autor - varchar(255) --> esto es lo que me gustaría enlazar con usuarios
+detail - text
+image - varchar(255)
+created_at - timestamp
+updated_at - timestamp
Any ideas?? Thank you!!!
You can use any field or combination of fields in a table to create a foreign key to it, as long as the following requirements are met:
Fields cannot contain null values (they must be
not null
).The field or fields must be able to identify one and only one of the records in the table. This is called unique identification .
In other words, if it is not the primary key, there must be a constraint
unique
on the fields; some engines also support having a unique index on them.This also means that you cannot have two records that have the same value, which makes sense, because if this were to happen, the engine would not be able to determine which of those two records a record from another table under the foreign key is associated with.
That said, the most common and natural thing is that the foreign key is made towards the field or fields of the primary key of a table, which already meet those conditions. You can then get whatever data you need from the associated record by doing a join between the tables, that's usually no problem.
So, if you decide to leave the field
autor
of the news and sales tables, I suppose you would associate it to the fieldname
of the tableusuarios
, so you must start by making sure that the column isnot null
and create a constraintunique
on said field:Now, you can already declare the foreign key, for example in
ventas
:I advise against this practice. In my opinion, you must have a very good reason to make a foreign key to a field that is not the primary key of a table.
Obviously, if you decide to use the primary key, you will have to change the field in the sales and news tables, for example by adding a new field and removing the old one: