Table design to Track Multiple Client Types

[TannerT]

Hello All!



I have a noob question that should be pretty easy for someone here I imagine.

I run a small computer repair shop and have decided to explore the possibility of using Access to track my Clients, Service Tickets & Invoices.

I have began working on some rough designs, but have a few general design questions regarding how to structure my tables.

I bill residential and business clients at different rates, and store different information on both. Some business clients have multiple contacts which I keep information on seperately, as well as their respective IT Assets. I also keep track of the businesses themselves for marketing purposes. In that regard I track things like primary industry and number of employees.

My original solution for this was to create three tables for Residential Clients, Business Clients, and Companies. I thought this would give me the most flexibility.

However, I get a little stumped when it comes to tracking service tickets and invoices from that point.
I would like to be able to look at all service tickets or invoices for a given client using a client ID, but I cannot figure how to manage this if I have clients stored in multiple tables. I would like for the client IDs to be uniform in format for simplicity but,... for instance, if I let Access do it's auto-number thing I could potentially have Business and Residential clients with the same Client ID.
Can I make Primary Keys on different tables unique among one another?

Or can someone suggest a better way to organize this data?

[TannerT]

Another thing I was just contemplating was organizing the tables as: Businesses, Users & Clients. Then I could use autonumber for the clients table which would really only store the client ID. I could use that table however to associate Invoices, Service Tickets and Devices to.
However, since a client can be either a Business or an Individual and I keep different information on each, can I have Access 07' differentiate between the two types and display the relevant information on say,... an invoice?

For instance,... if it is a business I would want the business name and the person who authorized the service. If it is an individual that would not be necessary but I would want to display first and last name,... Etc,...

If necessary I could design the front-end in VB.NET or C#, but I would like to do this all in access if possible.

[TheShabz]

You can try this. Lets assuming the following:
Business custs require info 1, 2, and 3
Companies require info 1, 2, 4, 5, 6
Individuals require info 1, 3, 5

Your tables will contain:
CustID (PK)
info1
info2
info3
info4
info5
info6
custType (FK)

Have all possible data fields for customers in there and only fill in required fields per custType. Now when you query and make reports, you can use custType as your identifier.

[TannerT]

Thanks so much for your reply.

The reason I decided against going that way was because of the extreme differences in the information I track.
Its not like its just one or two fields.
For instance, with businesses I track primary industry as well as number of employees.
With Residential customers I track things for demographic purposes like first and last name, DOB, Sex, Spouse name Etc...
The big thing however, is that businesses can have multiple users associated with them, with only one being the primary contact. If I have both users and businesses in the same table, I can't associate them with one another.

The only solution I can think of that is feasible is to create a Clients table (to track client numbers)
A companies table (to track companies of course)
and a users table to hold information on both residential clients AND business users.

Which brings me to another question,... Each record in the companies table will be associated with a client record,... in the users table my residential customers will be associated with a client record.
However,... the users that are not clients themselves, but part of a company,.... Anyone have an opinion on weather I should associate them with their company,... or directly with a client record? or Both?

[TheShabz]

Your "another question" really depends on the data. It's really your call as both options are possible.

As far as the table design goes, How bout:

tblCust
custNo (PK)
common fields, name, address, phone, etc

tblIndividual
custNo(PK) = tblCust.CustNo
unique fields to individual

... and the same for the other two.

you then create a 1-1 relationship between those tables. It's kind of a hybrid between the 1 table and the separate for each type approach. you have one table that contains all common information for ALL customers, and then break them off by type.

[TannerT]

Awesome,... thanks a lot for your help!!

[TheShabz]

Anytime. Please go ahead and mark the thread solved so others with a similar question can find the answers =]