The problem of designing customer and supplier databases

Question

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

At first I thought about using the Customers table and the supplier table.

Then, when I thought about banking transactions, I noticed that each transaction should be directed to a customer or supplier, so I thought about using a single Business table, in which I store both customers and suppliers.

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

If I use the Businesses table, I will need to use a business type column and combine the possible fields for all types of enterprises.

Any design suggestions?

Answer 1

The question you need to ask is information that is common to customers and suppliers. If the information (and the use of this information) is largely the same, then storing them in the same table is probably OK. If the information (or its use) is significantly different, then you should probably keep it separate and create a general idea between what is needed for banking transactions.

Answer 2

Customers and suppliers. Wrong. These suppliers and buyers are two sides of a standard commercial business relationship. The term "customer" is a concept that applies only to the marketing department - its meaning is based on contexts and therefore too abstract for any particular business process. It will work only if you define the Client as one type of relationship, for example, “Buyer,” which seems meaningless and confusing.

The idea that one organization / person can exist in both camps is completely random. If a business requires you to know that a particular party exists in both business relationships, they should be able to somehow control this information - and the control method should be dictated by the business - you cannot invent it. There must be a business rule allowing 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.

Answer 3

The question is, can your customers also be suppliers? In many enterprises, a client is an object that buys from you, and superin is a business that sells to you, and the same object can do both things at different times. They are known as “counterparties” in the financial industry, and I have never seen a financial trading system that distinguishes them in separate tables.

Answer 4

Both of your ideas are right and right solutions to your question. To find out the correct answer, you need to think about how to use your application usage patterns to decide which would be preferable. For example, will you list all banking operations more often than listing customers and suppliers separately?

Answer 5

The answer to this lies in two areas: a) are two types of objects (Customers and Suppliers) just two sides of the same coin or are they fundamentally different, and b) does this lead to a more convenient, reasonable database design?

You must consider the impact from both a business logic perspective and an RDBMS perspective. Does it make it possible to define foreign keys in the transaction table for both the Customer's tables and the Supplier?

Are you really going to search in your application when the customer result and the supplier result are mixed? If so, I would recommend just creating a view that combines common parts and separates tables. Keep in mind that other information, such as address or phone number, may be stored in other secret tables, so most of the general information will be reorganized anyway.

Answer 6

I don’t want to repeat what everyone else said, but I want to add that in your particular case of using the client and the provider, one thing that needs to be tracked depending on your domain is that the Client can be a supplier.

Suppose we are dealing with the sale of automotive parts. The dealership is a customer because it buys spare parts from other dealers (for example, if the parts do not take up space). Thus, the dealership can be both a client and a supplier.

I usually like to model relationships with suppliers, by defining a business or company, this determines who we are dealing with. Name, address, etc. Then I would define the Client who is the client, and the client is the client of the business, so you will have who the client is and who the client belongs to. Then you can decorate your client with additional relationship information.

You can do the same with the supplier. You can also abstract this out and have one relationship table, but it gets confusing and you lose some of the meaning without much benefit.

Answer 7

I think your best answer comes from the user domain. Is there any information about the customer and the supplier that is supported jointly by the same users (in the same roles)? If so, it is easier for them if they can change information in one place for both purposes. If not, you will force them to change each other's data, which will not help anyone.

The same problems arise if you have separate procurement and sales systems, and they need to interact with them; or have an Enterprise system (in this case, you probably should match what it does.)

Question: do you take your requirements from the Case History or the User Stories or ...?