Customers and suppliers database design issue

Question

I am developing a web application in which I will have customers and suppliers.

Initially I thought on using a Customers table and a Suppliers table.

Then when I was thinking on bank transactions, I noticed that each transaction needs to refer to a customer or a supplier, so I thought on using a single table named Business in which I will save both customers and suppliers.

If I use Customers and Suppliers tables when I want to list the bank transactions I will have to search in both tables to get the company name.

If I use a Businesses table I will have to use a business type column, and have the union of possible fields for all businesses types.

Any suggestions on the design?

Answer 1

Customers and Suppliers. Wrong. It's Suppliers and Buyers - two sides of a standard commercial business relationship. The term "Customer" is a concept that is only of use to a marketing department - its meaning is context based and therefore too abstract for any specific business process. It will only work if you define Customer as one type of relationship, such as 'Buyer' - which seems pointless and confusing.

The idea that one organization/person may exist in both camps is entirely coincidental. If the business requires you to know that a given party exists in both business relationships then they must be able to control this information in some way - and the control method must be dictated by the business - you cannot invent it. There must be a business rule that enables the business to know and record that the same party is involved in two different aspects.

It is not possible to create a data structure that works if it is not supported by business rules. It really is that simple.