I've got an existing SQL table structure that looks like this:

- order_id
- organisation_id
- [all the rest of the order data]

- organisation_id
- [all the rest of the organisation data]

- 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:

Option A

  • Add field orders.user_id, and then set either user_id or organisation_id to null for each order, indicating whether the order was placed by a user or organisation.

Option B

  • Add a table orderers (or similar) and then add fields organisations.orderer_id and 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?


Option B will actually cause you extra work on building the joins, but not necessarily worst performance, depending on your situation.
Consider this scenario:
- 1k organisations
- 20k users
- 1M orders
With scenario A you will have to take into consideration:
- you will have one more column on a 1M rows table
- you will probably want one index for user_id and one for organisation_id so if you want to find all the orders belonging to each you can find them faster. This means your inserts/deletes would be slower than on scenario B which needs only, in reference to your largest table:
- only one index on orderer_id.
I think best choice will end up depending on your specific situation, but take this into consideration.

  • That's a great thought - I hadn't considered the volume of records in each table. – Fabian Tamp Jun 5 '13 at 22:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.