How to organize a customer database

[jasonwood]

I'm looking to hear what some other users have done to organize their client databases where the nature of the business is made slightly more complicated due to circumstances like...

Companies may have more than one person who is authorized to purchase from you

Companies may have different contacts for different purposes (accts payable dept, etc.)

Here's my idea...

1. Each company only has one record in the customer database.

2. Names, department name, and phone numbers are stored in a separate database file, entered through a portal from the main customer record.

I am curious if this is the method others are using, and if so, what fields do you choose to include in the related records rather than the main record.

And then, what if it is just a simple individual that buys from us... it seems like having two records for them is a bit of a waste... perhaps there should be a "primary contact" in the main record and then additional contacts are entered through the portal.

--

Jason Wood

HeyWoody.com

[BobWeaver]

For my own data system, I use two files one for companies, and one for contacts. The contacts appear in a portal in the Company file. In the Company file, I have business name, address, main phone number, etc. Then, in the contacts file, I have the person's name, address, phone number etc. (if different from the company's).

If I was going to redo it, I might consider having both companies and contacts in the same file, with contact records related to the parent company record by a self-join. There would be a portal showing individuals, same as before. This would also allow you to have a hierarchical system where very large companies could have several child records for different departments or locations, and these records could then have child records for the individual contacts. Of course, this could get out of hand.

[Kurt Knippel]

If I was gonna recommend anything, I would say to keep the basic file structure as Company and Contact. Keeping in ming that a "Company" is really just a grouping of "Contacts", while a contact is generally an individual person.

Thus you could use the Company file for all of your groupings, some people even refer to this as an Organization, which is more true to what it is. Since a company is a type of Organization, as is a department, or a club, etc.

A self-join could be used to link seperate company or organization records together.

[BobWeaver]

The reason I brought up the idea of a single file is that over a period of time I found that both my Companies and Contacts files ended up with such a similar field structure that it seemed that having two separate files may be of no real benefit.

As Jason points out, the situation of having a contact who is not employed by any company would require a dummy record in the companies file and a record in the contacts file. In a single file you could have a single record.

Mind you, I'm still in the daydreaming stage on this one. Putting it into practice might create a lot more problems than it solves.

[jasonwood]

Thanks for bouncing back your ideas. This is really helpful.

Yes, I have considered using self-joins, but I also fear it could become too complicated. One of my reasons for doing this is to ensure that all sales for a single company are placed under one customer file, so that A) we can see the complete customer history from one place; and if we issue statements, we will only have to issue one statement per customer!! If we use self-join's to achieve this, we still risk people invoicing the wrong file (unless we deny it in the script... is it worth it?)

I think what I will do is this:

In my main customer database have all the basic info plus a primary contact. Then relate to a contacts database for additional contacts only.

The additional contacts database would contain Name, Department, Address (if different), Telephone, Extension, e-mail, Contact Type (billing, sales, shipping, etc.), and Permission level (as perscribed by the company)

The Permission level is helpful because now companies can lay out exactly which employees are allowed to deal with us, and how (eg: purchase, pickup, etc.), so we'll never have to worry about them complaining that an employee bought something without authorization.

So... When you create an invoice from the customer database, you could then relate back to the contacts database to choose a "Sale contact", "Shipping Address", or even to make note of who physically picks up the product.

The only thing that will make this method more complicated is the FINDING. I think the find would have to be a little more heavily scripted so that you can still search by phone number or name. You'd want filemaker to search in the primary file and (new request) in the contacts database the portal so that the customer file will be found even if the phone number you're looking for is only stored in a contacts file.

I think this could work nicely!

Thanks guys!

--

Jason Wood

HeyWoody.com