Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
I know that question is opinion based but sometimes i don't know what is the best way or the right thing to do, i will give you an example for what im thinking now:
If you have a website the as Admins, Clients and Orders tables, and in the Orders table you will have to add an order with the respective client, the admin responsable for that order and who (admin) created that order, my question is, is the best away to do it make for all 3 fields "client_id, createdby_id, responsable_id" in separated pivot tables with relationships, or in one pivot table, or even in the Orders table?
An order has one and only one responsable_id.
The same goes for the cliendt_id (an order is only ordered by a client) and created_by (only created by one person).
I'd go for the simplest approach, keep these fields in the orders table and make them foreign keys to their respective tables. No pivot tables for that.
There is no need for a pivot table here.
But for exemple, in what situations is better a pivot table?
pivot tables are only needed for many to many relationships. Here it is three belongs to relationship.
@manelf you'd use a pivot table in cases where you might have multiple admins running a client's account. Or in your case, if clients can make changes to their orders and you want to see what the original state of the order was, and then track the changes.
Please or to participate in this conversation.