Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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:

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.

Evaluation

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?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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