Database Table related to two tables

Question

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

• Bart van Ingen Schenau: 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?
• Walter Mitty: 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.
• R. Gomez: I updated the question. An order refers to ONLY a customer or a supplier, it can't refer to both.

Answer 1

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.

• TheCatWhisperer: in fact, two tables is usually faster... smaller indexes to search through

Answer 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.