Need Advice

[Ray]

Hi All,

I am thinking of making one table for customers and venders
What are your thoughts on this approach?

Thank you
-Ray

[Ray]

RE: Need Advice

Hi All,

Just to clarify y I want to do this because I am thinking
If we make a separate table for customers and another for venders and another for suppliers we end up have 3 tables
With exactly the same info name, address, ECT I am thinking
Y do this if we can make one table and have a control-
Field to select weather it�s a customer, vender, ECT and
Cut down on the number of tables and just make separate forms for each.

-Ray

[Pat Bremkamp]

RE: Need Advice

Ray,

Like everything else, its a trdeoff. In our case, the supplier, vendor and customer are sometimes the same company. There is also the situation where in a non-profit auction the same person could a board member, donor, table sponsor, bidder, attendee, and on and on.

So, we started with logical fields in each record for each category, but it seems like there was always another category to add. So, we moved to a set with "category" as the primary table and company and person as child tables.

That. of course. brings its own problems.

So, it depends on the nature and stability of your business.

Pat

[Stephen Williams]

RE: Need Advice

This is what QuickBooks does. Seems reasonable.

[Ray]

RE: Need Advice

Hi Pat, Stephen

Thank you for your replies, Stephen I know Qbooks does
This hence this could be more work in the long run but
Would reduce the database size by having redundant field-
Names/tables so I need to sit on this idea a little and see
Hew else comments on this approach.

Thanks
Ray

[Mike Konoff]

RE: Need Advice

Ray,

The concept of having one table for customers, vendors, and suppliers is interesting. In my business, I would consider vendors and suppliers to be the same.

Also, it is possible to SELL to a company that you BUY from, so if you had two tables, you'd need one listing in each table for the same company - your idea eliminates this need.

Would you have a field that tracks the vendor number that your customers assign to you? Would you have a field that tracks the customer number that your vendors assign to you? Would there be one field to accomodate this, or two?

How would you handle payables to a vendor and receivables from a customer, or payables/receivables from a company that you buy from and sell to?

I think I like this approach. Thought provoking, nonetheless.

Mike

[JerryBrightbill]

RE: Need Advice

Ray

With current computers, database and table size is not much of an issue. However, data integrity is a big issue. If I have a list of companies that could be a supplier and a customer, I try to have one list. Then I have only one to maintain.

Rather that creating a bunch of logical fields in the "customer" table or whatever, I create a couple intermediate tables. For example, I may have a supplier table that has only a list of supplier ID numbers. I create a set with the supplier table as parent and the company table as child. I may have a customer table that has only a list of customer ID numbers. The company table might use the same ID for supplier and customer or may use a different field for each. Again, I just link it as a child.

The result is that the "supplier" list and "customer" lists are unique, but use the same data.

Jerry

[Peter.Greulich]

RE: Need Advice

Ray,

If it were me, customers, vendors, and suppliers would be one table. Have a field called "type" w. a simple lookup that fills in "customer", "vendor", or "supplier". You could query on the type. Or as Pat suggested - 3 logical values, if one entry could be more than one of the types. I have had the same issue - Clients & Utility Companies. I ended up combining the two tables because the maintenance issues were totally ridiculous and out of control. That was at least 4-years ago and everything has been working great.

Peter

[Ray]

RE: Need Advice

Hi All,

Thank you very much for all your comments, I just need to
think this threw, as a customer could be a vender/supplier
and vise a versa I am just trying to see this threw and
the more I think the more confusing it gets.

If I do as suggested above make 1 header table with
client_id and make another header with customer_id then
link to a table called cust_name_add we would still have
to enter the record 2 times one for customer and one for vender if the same customer was a vender and vise a versa.

So the only thing I can think is to put all field necessary
for customer/venders in one table such as customer_id, vender_id, ect.

-Ray

[Jim Chapman]

RE: Need Advice

Hello Ray,

""If we make a separate table for customers and another for venders and another for suppliers we end up have 3 tables
With exactly the same info name, address,""

Don't know what you are defining as the difference between a vendor and a supplier, but:

I think the issue is a red herring. IMHO, the central issue is not 1 table or 3 table, but what are the information needs. What is the resource difference between one table with 300 records, or 3 tables with 100 records each? Not much by todays disk standards. If you indeed have the exact same info needs for all three categories, then it needs to go in one table. Certainly there would be some overlap in things like name, address, etc., however, I would be suprised if the info needs for venders, suppliers, and customer are exactly the same. Surely there are different info needs for these three categories unless your database is quite simple. Down the road as your database increases in capabilities and you start needing different bits of information for the different categories, each record would have to contain all the fields for each of the three categorie's different info bits.

My guess is that right now, it might appear that one table is appropriate, but what will it look like down the road. Unless your needs are very simple, I think the three categories have fundamental differences in information needs. For me it is a no brainer, three tables.

Just thoughts,
Jim




ECT I am thinking
Y do this if we can make one table and have a control-
Field to select weather it�s a customer, vender, ECT and
Cut down on the number of tables and just make separate forms for each.

[Ray]

RE: Need Advice

Hi Jim,

Thank you for your reply, I was just sitting hear saying
to myself go with 3 different tables I am glad you agree.

-Ray

[Ray]

RE: Need Advice

Hi All,

Just for the record vender/supplier are the same things
sorry for the mystery.

-Ray

[AYelverton]

RE: Need Advice

Hello Ray:

Sure would simplify our lives if there was only one way to design systems. I ran into a similar situation many years ago where there were a lot of cross-over entities... vendors as customers, etc. To add to the complexity, most installations had multiple companies they were running on the software and those companies dealt with the same entities. Keeping each entity's information current in each company was an ongoing headache... if information changed for a vendor, it had to be changed every place that vendor was used. Here is a general overview of the model I developed then and I still use today:

I create one table which holds only the basic, static information about each entity such as name, address, telephone, tax id, contact, etc. Each entity is assigned a unique ID within this table. Note that there is nothing in the table to indicate whether the entity is a vendor, customer, employee, etc. There are situations where an entity may require more than one entry in the table, such as when invoices are mailed to one address and payments to another. I included a comment field for documenting these situations. You could implement a linking mechanism, but I never found it necessary.

As Jim Chapman pointed out, the information needed for vendors is different than for customers, etc. Instead of trying to build this complexity into the same table with the names and addresses, I use separate vendor, customer, etc. tables to hold the information each requires. The ID assigned the entity in the name and address table is used for the key in each of these tables so the only duplication of information is the ID. Before an entity can be used as a customer, vendor, etc., its ID must be entered in one of these tables along with the information required in that table.

Structuring your information like this doesn't require additional work on the part of the user since they are still entering the same amount of information even if an entity isn't a cross-over entity. It becomes a time saver when entities are cross-overs since the base information for the entity is entered once. An added benefit is that entity names are available anywhere you may need one and all it costs is the size of the ID field.

It took many, many hours of coding to implement the above when I originally did it. With A5, it is a walk in the park.

Hope this helps.

Allen

[Ray]

RE: Need Advice

Hi Allen,

Thank you for this wonderful description of your application; what do you call the table that holds the name, address, phone number, ect.

Now if I am a customer and you want to set me up how do you word this to the end user?

Do you say to the end user they need to setup a master name, address, and phone number, ect? then bring up another form and enter them as a customer creating two steps.

I am sorry I am just a little confused about having a master name, address table because it seems to me that
this needs to be setup first then I think on the customer/vender table would only lookup info on the master name address table.

Hope I am clear with my questions.

-Ray

[AYelverton]

RE: Need Advice

Hi Ray:

Being the creative person that I am, I call the name and address table "namst". I provide a fairly simple form for maintaining the information in this table. This form can be accessed directly off the menu or by a button on any of the forms where a name and address ID can be entered. A5 has an option that allows the user to revise a lookup table, but being a new user myself and under the gun to get a project up and running, I haven't taken time to explore how that works. Popping up the form is a simple way to accomplish what I wanted.

As to instructing end users, they would go into the customer information processing and pick and click the entity they want to set up as a new customer using the lookup feature A5 provides or a custom one if you want to roll your own. If the entity to be set up isn't in the master list, they would click the button to display the name and address maintenance form and add it.

Fairly straightforward process really. Don't make it more complex than it needs to be to accomplish the task at hand. That last bit of advice I have to give myself several times a day.

I should have provided a little warning note. You need to provide some mechanism so you know when it's safe to delete an entity from the name and address table. If a user deletes an entity, I just flag it and use a separate process to actually remove it. For example, you use an entity is as a customer. Before the entity can be removed from the name and address master, it must have been removed from the customer table and before it is removed from the customer table, it must have a zero balance and no activity for the last year or whatever criteria you use. You know the drill. In addition, once an entity is flagged for deletion, the entity cannot be added to vendor, customer, etc. table. Don't forget to allow a way for the user to reactivate an entity that was flagged for deletion but hasn't been removed from the table. Users do make an error every once in a while.

Hope this helps.

Allen