best way for business and individual type of customers?

[wjgo]

There are ways I can do this, but I am looking for the best practice. I operate a business and we and Accounts which made made up of two different kinds of customers, - Commercial and Residential. The Accounts are also consist of people, who are primary contacts, other contacts, employees, or people we bill the account to (residential and commercial). Like employees, Residential locations will also have people that live there, not just one person per residential account. BUT employees of a commercial location will have a job title.

I want to be able to eventually identify the account in forms and in reports by either the business name if the account is a "Commercial" type, or the last name of the primary contact for the Residential type.

Any suggestions? I am trying to avoid as many blank spaces as possible, and many empty/null fields in the database. I am running into issues such as not needing the addresses of people that work at a business because the business itself has a address (or I repeat the info too much), and naming the account itself (uses a last name of a person if residential and the name of the business if commercial). Naming the account could be done pro grammatically I suppose, but perhaps there is a better way to set it up.

Business:
Name


Address
Person (employee) (primary contact)
Persons (employees) (other contacts or regular)

Residential
Address
Person (primary)
Persons
Person (billing and address)

[orange]

There are a number of data models here.
Perhaps you will find one that is useful or can be adapted

http://www.databaseanswers.org/data_models/index.htm

[wjgo]

Thankyou. I have looked at that page but did not see what I was looking for.

Its interesting when trying to create a database, it forces one to think of data very differently. I am used to using a PIM (Palm Desktop) using asterisks and very long note fields for information on clients. But I think I am over complicating the issue.

At the root of all my "accounts" is every account requires and address, and a primary contact person with their phone number. The title of a person at a business can just as easily be considered the title/relationship of the primary contact of a residential account. For a report, I am sure there are fancy ways to channge the lable based on the type of account.

Then what I really need is just a table of "extra" contacts that link to the account table, where each has a title/relationship field and some way of numbering the order of preference for calling (if necessary for the account). Then another table for "special addresses" which would be off-site billing addresses. So when a report is created, extra address fields are not being repeated for contacts that work/reside at the account location.

I haven't been able to flesh this out on a db yet, so I hope it just comes down to that.

[orange]

Try going through the link here with your own data and see if it clarifies/helps
http://www.rogersaccesslibrary.com/T...lationship.zip

[wjgo]

I think I settled it down to this attachment. The only repeating field entries are for an address, but that is for a required address for all accounts, because a contact would become unassociated with an account then I might loose that address info...