Table Design For Contacts (1 Viewer)

I have to set up a database where i record employees, clients, sales agents, and distributors.

the challenge i face is that 1 contact could fulfil different functions in my database, ie a managing director is also a sales person, an employee is an administrator, an employee could be a client.

i also have different hierarchy in my contacts:

master sales agent
sales agent
prospects
clients

clients can also be be either an individual or a corporate entity

So if i want to normalise to the maximum i would like to avoid making client table, employee table etc. by having one contact table and then 'qualifying' each contact in separate ways, but not sure how to do that as i am totally new to that.

Please can anyone help?

thanks in advance

There are many template contacts databases available including one supplied with Access.
Or do a forum search including the threads listed at the bottom of this page

i also have different hierarchy in my contacts:

Click to expand...

The hierarchy of contacts is a chimera, an illusion that distracts you from your reality. A contact is a contact. If you track the contact any differently because s/he is a manager or Joe Schmuckatelli off the street, you have complicated your life and your business.

In your "welcome" posts you mentioned an order being related to a client and a contact. So let's further complicate your life: Can a client also be a contact?

The problem is that you need to look at roles that people can play, and you need to divest the roles from personal data. A client is a person or business. A contact appears to also be a person or business.

The way I see this (and I'm known for jumping to confusions, so take it with a grain of salt) is that you have persons who could be anything from a random encounter to an employee in your company to an employee in another company.

So you would have a table of people. The table would contain data about individuals NO MATTER WHAT they are to anyone else.

Then you would have a table of businesses. One of those businesses would be "self-employed" to represent the walk-in business. One of those would be your own company since your own people can be contacts.

Then you would have your list of orders. The orders are orders regardless of HOW they got to you.

Each of these tables would have individual ID fields, perhaps autonumbered, as their primary keys (PK). (Perhaps not, if you have some other preferred way of uniquely identifying persons, businesses, and orders.) To link them all together, you would have foreign keys (FK) in the other tables.

In the order table, you probably can only have one client. So an FK from order to client person tells you who is the point of contact for the client of the order. If the POC represents a company, that person's record can contain an FK to the company - and if not, you can have an FK to the "self-employed" record.

If you got the order through multiple contacts, you can have a junction table to list the persons who were your contacts for that order. Since one could imagine that a single contact could lead to many orders (particularly if the person is high in your "hierarchy"), and since you have already mentioned the multiple contacts per single order case, this SCREAMS "junction table."

Then you tie it all together on reports using appropriate JOIN queries.

This is ONE IDEA about how you might make this work. But remember, I don't see your whole business. This is a GUESS on my part that it might be applicable. If I'm off target, there is no need to take my word as gospel truth.

The key in this analysis is that I showed you a way to separate roles from entities like people and businesses. And THAT is the first step in making your database model conform to your business model.