3

I was writing an app(learning) and I found this problem.

I have 3 table Orders, Customers and Suppliers.

An order can only refer to a customer or a supplier and there is no conceptual difference between a customer or supplier order.

What is the best / most performant way to store it?

  • Create 2 separate tables: CustomerOrders and SupplierOrders
  • 2 fields in Crders: CustomerId and SupplierId
  • Use Id Ranges in Customers and Suppliers so that I know for example 1-1000000 are customers and 2000000-3000000 are suppliers
  • 2
    Can a single order logically be linked to both a customer and a supplier? Is there a conceptual difference between the order of a customer and the order of a supplier? – Bart van Ingen Schenau Dec 18 '17 at 10:49
  • Are we talking about one relationship here or two? Is the relationship between a customer and an order the same as or different from the relationship between a supplier and an order? They look different to me. – Walter Mitty Dec 18 '17 at 11:47
  • I updated the question. An order refers to ONLY a customer or a supplier, it can't refer to both. – R. Gomez Dec 18 '17 at 12:00
10

Using id ranges to distinguish customers from suppliers is a bad idea. Don't.

The problem is in the domain of accounting/bookkeeping. The standard in that domain is to have two binders, one for the invoices from suppliers and one for invoices to customers. That is one reason for having two tables.

Another reasons for having two tables is that in the order table there will be a field with the amount of the order/invoice. Is that going to be negative for supplier orders? Then what happens if you need to credit a customer for part of their order? Or you are credited part of a supplier order?

Two seperate tables does not get complex, at worst some duplication of logic. But do take care and see how you best can reuse code across the two tables, as there are similarities that can benefit from code reuse, but as you go along more differences will appear which are easier to handle with two tables.

With only one table each difference can lead to a lot of ifs or switches.

Two tables should not be slower in any way, but one table could lead to more complex queries that could be slower.

  • in fact, two tables is usually faster... smaller indexes to search through – TheCatWhisperer Dec 18 '17 at 17:24
2

All orders have a customer and a supplier. Perhaps you should have a field for both, each of which has a foreign key reference to a single Parties table, which in turn is 1:1 linked from Customers and Suppliers. This has the added benefit of allowing some customers to also be suppliers.

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.