Customer & Supplier - what if they are both?

[Mark-BES]

Currently my design consists of a table for customers and a table for suppliers. At first it seems logical. Or so I thought!

As my design is evloving I have noticed a problem. What if a supplier calls and says "I want to purchase a..." they then also become a customer!

Obvoius I know but do I leave the design (attached) as is or disolve the supplier table and then just add one field called [CustSupp] and have three options to choose from "Customer", "Supplier" or "Both"

I could then base any other forms like the [FrmCustomers] on a query that looks at the [CustSupp] field and has a Like "Customer" criteria

Whst is the best method. Any advice greatly received.

 

[The_Doc_Man]

Build a table with "business contacts" and put both customers and suppliers in it. You don't CARE (in this table) whether they are either, neither, or both.

In your list of invoices, link to business contacts. These are your customers based on the invoice linkage.

In your list of purchase orders, link to business contacts. These are your suppliers based on the PO linkage.

If you REALLY REALLY need to know without evaluating the linkages, you need two Yes/No fields - "IsCustomer" and "IsSupplier" - which you update the first time you get an Invoice or a PO to that party. Technically this is redundant data, though I'm not enough of a purist to tell you not to do it with extra "flags" anyway.

 

[getssmart]

Build a table with "business contacts" and put both customers and suppliers in it. You don't CARE (in this table) whether they are either, neither, or both.

In your list of invoices, link to business contacts. These are your customers based on the invoice linkage.

In your list of purchase orders, link to business contacts. These are your suppliers based on the PO linkage.

What happens if you need to link both a supplier and a customer on the same form? This is the exact same situation I have.

 

[gemma-the-husky]

dont confuse customers and suppliers. They are not really the same, and very few accounting systems would expect them to be.

customers are linked to accounts receivable/debtors/sales orders
suppliers are linked to accounts payable/creditors/purchase orders

both will have distinct identifiers

if they happen to be the same legal entity, its coincidental

Just because an employee happened to be a customer you wouldnt expect to link his employee address to a customer table, would you?

If you really need to manage linked customers/suppliers use a separate table, as generally there would be very few such relationships in any normal situation.

 

[neileg]

dont confuse customers and suppliers. They are not really the same, and very few accounting systems would expect them to be.

customers are linked to accounts receivable/debtors/sales orders
suppliers are linked to accounts payable/creditors/purchase orders

both will have distinct identifiers

if they happen to be the same legal entity, its coincidental

Just because an employee happened to be a customer you wouldnt expect to link his employee address to a customer table, would you?

If you really need to manage linked customers/suppliers use a separate table, as generally there would be very few such relationships in any normal situation.

Sorry, gemma, you are completely wrong. An entity is an entity. The relationship with your company may be that of a supplier or a customer but that is down to the relationship. Low end accounting packages can't cope with this concept, but high end ones recognise the true situation. There's absolutely no reason why you can't, and virtually no reason why you shouldn't use a single table for these entities. They will be linked to a supplier account, or a customer account or both.

In fact, you could have a situation where you don't distinguish between supplier accounts and customer accounts. I've not seen this in practice, but there's no programming or accounting reason why you couldn't do this. You would simply classify those with a net debit balance as debtors and those with a net credit balance as creditors in your balance sheet.

 

[The_Doc_Man]

What happens if you need to link both a supplier and a customer on the same form?

In my scenario, they are two different links to the same (entity) record from two different sources. One link is a record that is associated with being a supplier. If you are linked to a "supplier"-type record, you are a supplier. The other link is a "customer"-type record. If you link to one of these, you are a customer. There is absolutely no reason why you couldn't do this.

Gemma's response is correct only for Gemma's application at most, and otherwise represents a narrow view of entities. In reality, a business contact is a business contact whether we are talking sales, service, customer, or consultant. You keep about the same amount of data (name of POC, address, phone, shipping address, gender of first-born child, etc.)

The real world classifies people by how you interact with them. So if you have links from sales, service, customer, or consultant tables, you have identified an INTERACTION - and that is what you really wanted to know. This interaction is why you have RELATIONAL databases.

In gemma's narrow view, you would have to duplicate a LOT of data if you have people who both sell AND buy from you. By recognizing the function of the relational pointers (foreign keys, if you prefer) in the various other tables, you avoid unneeded duplication.

 

[boblarson]

Amen Doc Man!

 

[gemma-the-husky]

Thanks for your thoughts. I see how you could have a single table including addresses and other data for general use, and have reassessed my view.

Practically, however I suspect that such a solution with a single address table would be implemented very rarely. Anybody know any decent real world solutions that combine addresses of various sorts into a single table?

 

[neileg]

Practically, however I suspect that such a solution with a single address table would be implemented very rarely. Anybody know any decent real world solutions that combine addresses of various sorts into a single table?

Yes. If you look at any of the ERP or ERP-like systems, you will find this sort of approach. You'd be looking at things like SAP, Oracle Accounts, CedAr etc. Once you're in the realms of turnover in the £100m plus area, the distiction between customers and suppliers, not to mention factors and agents tends to get pretty blurry, especially when you've go lots of small value transactions.

My own organsiation uses CedAr but because we have own own bespoke sales ledger, we can't take advantage of the single address table. Causes us no end of bother.

 

[The_Doc_Man]

Gemma, the U.S. Navy Reserve uses a single personnel table for officers and enlisted. We use links (foreign keys, if you prefer) to the officers in charge for each unit rather than duplicating data. This is an ORACLE db, not Access, but the concept is the same.

Having separate supplier and customer tables makes sense only in a very few cases. Like, if you inherited a pile of poop as a database and can't update it because of previous work investment. Or if you have some regulatory body up your butt about how to structure certain classes of data.

When you have a free choice, minimize the number of tables and maximize the number of FK links. Use the FK links and the implied relationships to characterize a person's role(s).

 

[RV]

Gemma, the U.S. Navy Reserve uses a single personnel table for officers and enlisted. We use links (foreign keys, if you prefer) to the officers in charge for each unit rather than duplicating data. This is an ORACLE db, not Access, but the concept is the same.

I happen to work with ORACLE systems and customer and supplier data indeed is split up into separate tables.
So the concept does not always necessarily apply

RV

 

[The_Doc_Man]

The question is why they are split. The answer depends on what you do with each.

If there is a good reason to split them, do so. If they are split just because they were split, then your reason for splitting was arbitrary and not driven by a particularly good business reason. My comment remains: If you have a good reason to keep suppliers and customers separate, do so. If you have a free choice in the matter, don't split them.

ORACLE designers have no lock on good design, trust me. A good design transcends the tool used for the DB.

 

[RV]

If they are split just because they were split, then your reason for splitting was arbitrary and not driven by a particularly good business reason.

No splitting done by yours sincerely
It's simply how the database comes...

ORACLE designers have no lock on good design

As compared to?
I've seen plenty of worse designs in my career, trust me
I can't imagine that a world leading company on ERP systems and databases malpractice their designs for no good reasons...

RV