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

Both of your ideas are valid and correct solutions to your question. To know the correct answer you'd need to consider your app's intended usage patterns to decide which would be preferable. For example, will you be listing all bank transactions more often than listing Customers and Suppliers separately?

Answer 2

The question you need to be asking is what information is common between customers and suppliers. If the information (and the use of that information) is largely the same, then storing them in the same table is probably OK. If the information (or use thereof) is largely different, then you should probably store them separately and create a common view between containing whatever is needed for the bank transactions.

Answer 3

The question is, can your customers also be suppliers? In many businesses, a customer is an entity that buys from you, and a suplier is s business that sells to you, and the same entity can do both things at different times. In the financial industry, these are known as "counterparties" and I've never seen a financial trading system that differentiated them into separate tables.

Answer 4

The answer to this lies in two areas: a) are the two types of objects (Customers and Suppliers) simply two sides of the same coin or are they fundamentally different, and b) does it result in a more maintainable, sensible database schema?

You have to consider the impact both from a business logic standpoint and from a core RDBMS standpoint. Is there value in being able to define foreign keys in the Transaction table to both the Customer and the Supplier tables?

Are you really going to be doing searches in your application where a customer result and a supplier result would be intermingled? If so, I would recommend just creating a a view that unions the common parts and keep the tables apart. Bear in mind that other information, such as an address or phone number, can be stored in other discreet tables, so a lot of the common information will be refactored away anynow.

Answer 5

You'll face a similar design argument when dealing with class hierarchies and NHibernate:

http://www.hibernate.org/hib_docs/nhibernate/html/inheritance.html

Answer 6

I don't want to repeat what everyone else has said, but want to add that in your specific use case of customer and supplier one thing to watch out for depending on your domain is that a Customer could be a supplier.

Let's pretend we are dealing with selling automotive parts. A dealership is a customer as they do buy parts from other dealerships (When parts are backordered for example). So a dealership can be both a customer and a supplier.

I typically like to model customer supplier relationship by defining a Business or Company entity, this defines who we are dealing with. Name, Address etc. Then I would define a Customer which a customer is a business and a customer is a customer of a business so you would have who the customer is, and who the customer belongs too. Then you can decorate your customer with additional information about the relationship.

You can do the same with the supplier. You can also abstract this out and have a single relationship table but it gets convoluted and you loose some of the meaning without much gain.

Answer 7

I think your best answer comes from the user domain. Is there any of the customer and supplier information that is maintained in common, by the same users (in the same roles)? If so, it's easier for them if they can change information in one place for both purposes. If not, you've got them changing each others' data, which will not make anyone happy.

Same issues arise if you have separate purchasing and sales systems, and need to interface to them; or have an Enterprise system (in which case you should probably match what it does.)

Question - are you deriving your requirements from Use Cases or User stories or ... ?

Answer 8

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.