One Table for customers and vend

[Rayr]

Hi,

I am creating a brand new application and trying to think of better ways to design my application, what I would like to know, is it a good idea to make one table to store customer and vendor header info like name, address, account no., credit limit, ect. or this is NOT a good practice and should be keeped in two different tables?

[seaken64]

Re: One Table for customers and vend

Hi,

I am creating a brand new application and trying to think of better ways to design my application, what I would like to know, is it a good idea to make one table to store customer and vendor header info like name, address, account no., credit limit, ect. or this is NOT a good practice and should be keeped in two different tables?

Ray, you are way ahead of me in database development so I feel uncomfortable sharing my input. But I am curious what others might have to say on this subject. I have had this same question many times.

I have seen tables called "NAMES" that seem to be used to hold all the contact header information regardless of the type of contact. But I prefer to keep them separate since I treat customers and vendors differently in my business. I sell to customers and I buy from vendors. Having these names in two different tables makes sense to me and allows me to set up relationships to other tables differently depending on whether the activity is selling or buying.

I suppose it would make a difference if you routinely sold items to your vendors (which makes you vendors your customers) and also routinely purchased items from your customers (making them also vendors). This is rare in my business and if I sell to a vendor I just enter a record into the customer file and that contact ends up with two records, one in the VENDOR table and one in the CUSTOMER table.

The number of times that each contact may end up in both tables is small so I like the clean separation of names into the two types. I think this also follows the suggestions of relational database theory, which I have a hard time following. But in general I understand that any data type that has it's own category should be in it's own table.

That's my two cents.

Sean

[Bill Parker]

Re: One Table for customers and vend

I am in the process of updating a system now and this version has vendors and cusomers combined in a contacts table. Previous version had them separate. User confusion is what pushed us to a combined table, but there can also be confusion with a combined table.

In favor of combining is that there is no possibility of conflicting address, email, etc. between the files. But you must decide how lookup list should be displayed. We have fields for vendor, customer, etc. that are used to filter the lookup, but what if those fields are not accurate, or have changed? We use a custom lookup so user can toggle to display all records if they don't see a contact record they think has been entered.

Quickbooks uses separate tables, but requires that the contact name be unique between tables since some displays combine all names. This seemed the worst of both worlds to me.

Bill.

[MoGrace]

Re: One Table for customers and vend

Hi Bill,
I use one table named Directory to hold all contacts and assign a Type to each. I use an auto increment DIRCODE and an editable DIR_ID for displaying to the user. The foreign keys used in the linking tables is where I differentiate by Type to filter my lookup lists. I have SHIP_ID, VEND_ID and CUST_ID which can look up the same DIR_ID field from one table and fill in the hidden SHIPCODE, VENDCODE or CUSTCODE so I can have more than one link in those tables.

For example my packing slip form has both a customer and a shipper. My user types in the DIR_ID name he knows (like QB) for each one, and doesn't have to deal with the true link which is DIRCODE. If he wants to edit the DIR_ID later, the DIRCODE field doesn't change which allows a post to the related records in the other tables. So my PS_HDR table example has 4 fields, which gets a little more complicated for designing but works intuitively in my forms.

In my Phone List form the user can filter the list by Type and the form he zooms to displays dependent upon the Type but to the same table. Which makes the form design easier also.

I started with multiple tables too, but like this better. At least the fields my scripts have to reference for the Directory table are always the same ones.

[Bill Parker]

Re: One Table for customers and vend

I also keep the real linking field hidden, because in my case it is a guid. That is why the custom lookup is required - to insure if the lookup list is opened again that the linking field is used to position the lookup list, not the displayed name field which is not guaranteed unique.

I previously entered a new feature request where a "linking field" could be defined separate from the lookup field. This would be different than the coded lookup that is now available. In a coded lookup the linking value is stored in the field, but the "name" is displayed. I needed both.

Bill.

[Rayr]

Re: One Table for customers and vend

Thank you to all that replyed. I will try using one table called names.

[rmcgaffic]

Re: One Table for customers and vend

Hear are some helpful links from Amazon.com:

The Data Model Resource Book, Vol. 1: A Library of Universal Data Models for All Enterprises (Paperback)

The Data Model Resource Book, Vol. 2: A Library of Data Models for Specific Industries (Paperback)

If you find yourself designing a system in an area where you have not worked before, these books are invaluable. The problem in this post is covered in Chapter 3 People and organizations

http://www.amazon.com/gp/reader/0471380237/ref=sib_dp_pt#reader-link

Bob McGaffic
Pittsburgh

[sparkey]

Re: One Table for customers and vend

I think that the proper way is to keep the VENDOR and CUSTOMER tables separate.

Just like designing any database, data should be broken down into its individual segments. CUSTOMERS / VENDORS / SALES ORDERS / PURCHASE ORDERS

They all have unique characteristics about them. It may work, initially to have them share a table, but it will may make it difficult to add feature enhancements later on. But, this is just my opinion on database design. It may add more work initially but long term, it allows for much easier maintenance and modification.

[Rayr]

Re: One Table for customers and vend

I think that the proper way is to keep the VENDOR and CUSTOMER tables separate.

Just like designing any database, data should be broken down into its individual segments. CUSTOMERS / VENDORS / SALES ORDERS / PURCHASE ORDERS

They all have unique characteristics about them. It may work, initially to have them share a table, but it will may make it difficult to add feature enhancements later on. But, this is just my opinion on database design. It may add more work initially but long term, it allows for much easier maintenance and modification.

Hi Mark,

Thank you for your input, I respect what you are saying here to a point, customers buy from you and vender we buy from, that said the customers and vendors both have allot in common as for the structure of the table, they both have name, address, city, state, zip, phone, fax no.,account number, credit limit, terms, ect. so my thinking process right now is to make a name table to hold all that info with a name type controle field, aka vendor, customer, other then make sets one with purchase order and one with invoices, ect.

I am still thinking this threw and playing with design ideas and you may be very well right or I may hit a brick wall at some point with my design, I dont know yet. If anyone else has something to add to my design thoughts please let me know.

[MikeC]

Re: One Table for customers and vend

Mark and....,
I believe it to be only a matter of personal preference and db maintenance. Take this as an example:

A db has customers/businesses. Businesses can mean--clients (which can also be broken down into Active and InActive), vendors (suppliers), technical (fixing or diagnostic), personal, and probably many more.

All of these have the same requirements regarding the information needed such as Address, Phone, Contacts, etc..
All of these can easily be filtered to only show what is wanted ....I usually do this via a combo dropdown box.

As such I see no reason to have them in separate tables unless you have so many that the 2gb limitation could come into play. But if wanted a separate table could be created for each----but then think of the increased set size that possibly could result which would/could cause problems (complex/large sets seem to have caused problems for people).

For me, it seems that having them all in the same table would be a more normalized db.

Just another opinion! :)

[sparkey]

Re: One Table for customers and vend

Mark and....,
I believe it to be only a matter of personal preference and db maintenance. Take this as an example:

A db has customers/businesses. Businesses can mean--clients (which can also be broken down into Active and InActive), vendors (suppliers), technical (fixing or diagnostic), personal, and probably many more.

All of these have the same requirements regarding the information needed such as Address, Phone, Contacts, etc..
All of these can easily be filtered to only show what is wanted ....I usually do this via a combo dropdown box.

As such I see no reason to have them in separate tables unless you have so many that the 2gb limitation could come into play. But if wanted a separate table could be created for each----but then think of the increased set size that possibly could result which would/could cause problems (complex/large sets seem to have caused problems for people).

For me, it seems that having them all in the same table would be a more normalized db.

Just another opinion! :)

I would politely disagree to the extent that by having customers and vendors in the same table. If they both are in the same table you have to add extra logic in your application to distinguish between the two. Also, when adding in new customers or vendors, each record has to be marked as to its type. A simple keystroke error could have a vendor record being marked as a customer and vice versa.

By using separate tables, customers are always customers and vendors are always vendors. Not to mention how to handle a situation where a customer could also be a vendor.

A better thought might be to have three tables:

VENDOR
CUSTOMER
ADDRESSES

and then set-up primary and foreign keys so that all the address information can be combined into one table, but the unique customer and vendor data would be separate. ;)


BTW, most of my opinions stem from some past experience dealing with clients who, for all their knowledge, are sometimes completely inept when using computer software. Thus the KISS method of design.

Keep It Simple Stupid, ( not for me but for the end user. )

Database Skills Blog

[MikeC]

Re: One Table for customers and vend

:)

Again I really do think it is more of a personal preference as each method does have its pros and cons. You mentioned the "cons" of having all in a common table but when I look at the "pros" they outweigh them --for ME that is! :)

By far the best thing I like when having all in the same table as a user of a software that have various types of companies is this:

When in a form a simple combo box that shows "Type" of company can be used to filter the same form and only the one table/set the form is based on is opened.


Along with what we are discussing here and should be mentioned, if for no other reason than having uniformity within an application, is whether to have all the transactions in the same table or separate--that is having both income and expenses in one table or two....which also would apply to the allocations of the transactions whether being income allocations or expense allocations. The same goes for various Types of products for that matter.

My point is, where would you draw the line??? Matter of preference and database maintenance as far as I am concerned!! :)

And if we agree to disagree absolutely not a problem...always a good thing to have varying opinions to be able to get more out of an endeavor!

[DaveM]

Re: One Table for customers and vend

Ray and all,

Kinda from the old school here.

What "product(s)" are you selling. If the vendors are not customers, and even if they are. seperate tables makes better sense and maybe one for products.

I would NOT seperate addresses into seperate tables unless a lot of either had a lot of them and only then it was like shipping addresses.

Dang, Id like to see what the data type and what the flow of data really needs to be. Who to and where from is essential.

We need more info before anybody makes and real firm suggestions.

I do keep Insurance agents, banks, and vendors in one table for lookup purposes. the name and some other stuff is shared and some is seperated into diff fields, but there is a field that is an "id" for the type of vendor/etc. Easier to filter.

I see the above as customer.table - Vendor.table(for lookup) - product.table(for lookup) for the items. Sets can be setup for the various needs. this can be easy if done right.

.

[KenDowns]

Re: One Table for customers and vend

Hi Mark,

Thank you for your input, I respect what you are saying here to a point, customers buy from you and vender we buy from, that said the customers and vendors both have allot in common as for the structure of the table, they both have name, address, city, state, zip, phone, fax no.,account number, credit limit, terms, ect.

Vendors and Customers are superficially similar, but when it comes to application logic they have nothing in commmon. Therefore every single function you write for each of them will require code that prevents that function from happening for the other. Printing an AR report? Better make sure to filter out the vendors. Entering orders? don't let a vendor have an order! Printing checks? Not to customers! That credit limit field? Better make it disappear when they are looking at vendors. Will you have different security groups who can see one but not another? Add another where clause to every single query in your application that hits the table.

Hopefully this gets the idea across. You save a small amount of work and face endless headaches and bugs forever after trying to keep these things separate.

Another thing: were you planning to use foreign keys into or out of this table? So a customer is required to have a customer type but the vendor is forbidden, how do you enforce that? I don't want to know :o

This type of classic mistake can be avoided if you know a little secret: the simplest possible applications occur when you put items together into tables based on their purpose and function, not their field names. Doing it this way will often create tables with fields in common, but if you try to get "efficient" by combining the tables you lose so much in other areas that it is a big loss more than anything else.

Somebody else referenced my blog on this thread, but here is the particular article that goes through this: http://database-programmer.blogspot....s-this-is.html
The discussion of combined tables begins about halfway down.

[seaken64]

Re: One Table for customers and vend

Thank you Ken, great resource! I've got more reading to do!

Sean