I've got an existing SQL table structure that looks like this:
Orders - order_id - organisation_id - [all the rest of the order data] Organisations - organisation_id - [all the rest of the organisation data] Users - user ID - [all the rest of the user data]
I'm trying to change the
Orders table so that an Order can be linked to either an
Organisation or a
User. I've got two ideas:
- Add field
orders.user_id, and then set either
organisation_idto null for each order, indicating whether the order was placed by a user or organisation.
- Add a table
orderers(or similar) and then add fields
users.orderer_id. Then, users and organisations are both orderers, and orderers place orders.
Option B's role-based approach seems to make good sense and seems to be more extensible, but I just can't help but think about the hassle the extra
JOINs are going to cause, in terms of performance and SQL complexity.
What is the best way to go about this?