Hello community,
Let’s say I have an Invoices table. Those Invoices can be owned by a Users table, a Groups table and a Compagnies table.
What’s the best approach? One foreign key named FK_owner or three? One for each table.
Pro/Cons?
Thank you.
Hello community,
Let’s say I have an Invoices table. Those Invoices can be owned by a Users table, a Groups table and a Compagnies table.
What’s the best approach? One foreign key named FK_owner or three? One for each table.
Pro/Cons?
Thank you.
If you think what you want to accomplish can be done with a single FK, you can as well use just one “owner” table.
Those are three columns in Invoices and 3 different FKs
Your right, can’t do that in My-SQL (one foreign-key for two+ tables)
This is asked a lot:
Thank you.
Apart from that, I think it is not a good practice to see such a thing.
I imagine this is far gone now, however, I think you can handle this kind of relationship as follows. Your users, groups and companies can be seperate tables but they should each have a common column, e.g. object_id, which is a foreign key to a table, object.
The columns in the object table would typically be object_id and object_type_id, where object_type_id is a foreign key to a table object_type with columns object_type_id and object_type_name (where the names will be user, group, company, etc).
Now you can connect your invoice table to just one table - the object table.
Further details for that object can be pulled from the related user, group or company tables and which table to pull from can be determined by matching against object_type.
Something like this:
