Sales & Purchase Order Database (1 Viewer)

Just after a little advice in case anyone has ever built something like the subject line.

I have done a few different ones and have always had a separate table for customers and suppliers. I have started to notice that some people have a customer who is also a supplier. Would there be any benefit in having a single table and a field that allows to be either customer, supplier or both?

Or would it be better practice to continue with the trusted method I already base this on?

~Matt

Would there be any benefit in having a single table and a field that allows to be either customer, supplier or both?

Click to expand...

possibly, really depends on how your db is intended to work. I've used both methods in the past. A single table takes more managing and won't really show much benefit is space saving unless you have many customer/suppliers. Also have to consider data security - do you have users who works with customers and other users who work with suppliers?

If you stick with separate tables, you could include a flag field to provide the supplier PK in the customer table (or visa versa), doesn't require a flag in both tables.

MattBaldry said:

Would there be any benefit in having a single table and a field that allows to be either customer, supplier or both

Click to expand...

if you're using a server oriented database application system, I believe some of them have capabilities like this, but I would doubt that anyone would recommend it. you can obviously do this in access to by modifying it to an extreme via various UI and VBA methods, but it's probably not worth it. IMO, i would just stick to what you've been doing.

The advantage when your suppliers are also customers is that you have one point of change. Typically everyone is considered a customer and suppliers are tagged with a SupplierFlg. The only purpose of the flag is to filter the combo from which a supplier is picked.

If you keep many different attributes for suppliers that you don't keep for customers, you would probably be better off using separate tables but I've found that in most applications the customer and supplier tables are virtually identical.

I've also created applications that used an Entity concept. Employees, Students, Guardians, Teachers, Vendors, etc are ALL Entities and certain common basic information is kept for all of them. In this case though, you can't get by with a "SupplierFlg", since each Entity can actually have multiple roles so you need a m-m table where an Entities roles are stored. It is the role table that is used to control combos. In order to ensure that every entity has at least ONE role, we defined a defaultRole field which was entered with the Entity and generated an entry in the Role table. Using the entity concept, you sometimes need 1-1 tables to keep the columns that are specific to only a single entity type. So your queries, when working with Guardians for example, would join (left join always) to the tblGuardian which contained only the attributes specific to the guardian entity.