Tracking contacts and calls for several entities

[foxtrot123]

I'm trying create a database to track contact information and calls (date, time, outcome) placed to:

1) businesses (e.g., calls to a main number),
2) business front desk people (e.g., a business may have 0, 1, or many front desk people)
3) business employees (calls to each employee), and
4) freelancers (calls to individuals who are not part of a business).

Business front desk people and employees usually, but not always, have the same phone and fax number as the main business.

For businesses, I also need to track what type of business it is (e.g., finance, textiles, etc.).

To track the contact info., here's what I have so far:

tblBusinessType
---------------
BusTypeID (PK)
BusType (e.g., Finance)

tblBusinesses
-------------
BusID (PK)
BusTypeID (FK to tblBusinessType)
BusName (e.g., the XYZ Group)
BusAddress...

tblFrontDeskPeople
-------------------
FDID (PK)
BusID (FK to tblBusinesses)
FDLastName

tblEmployees
--------------------
EmployeeID (PK)
BusID (FK to tblBusinesses)
EmpLastName

tblFreelancers
--------------
FreelancerID (PK)
FrLastName

tblNumbers
----------
NumberID
BusID (FK to tblBusinesses)
FDID (FK to tblFrontDeskPeople)
EmpID (FK to tblEmployees)
NumberType (e.g., main, fax, cell)
NumberFull

I realize tblNumbers isn't too graceful, but I think it'll give me the flexibility I need without getting things overly complicated (i.e., many-to-manys). Or will this give me major problems? (I don't need to do any fancy queries or reports with phone numbers.)

As for tracking calls, should I use an approach similar to tblNumbers? I realize that for each record 2 of the 3 FKs will always be blank, but I can handle that. I think it will still allow me to make an easy-to-use GUI and track call info for each entity.

But perhaps I'm way off. Any suggestions?

 

[gemma-the-husky]

have a look at the MS contacts application database

it is very close to what you are doing, and will save you a lot of trouble

http://office.microsoft.com/en-us/templates/TC010178391033.aspx

 

[foxtrot123]

have a look at the MS contacts application database

it is very close to what you are doing, and will save you a lot of trouble

http://office.microsoft.com/en-us/templates/TC010178391033.aspx

For the call tracking, that is good model when you have only one entity (e.g., everyone called "contacts"). I have four.

Also, the MS template doesn't normalize the telephone information. It hard codes each phone type into the contacts, which severely limits the database's flexibility when it comes to adding more phone numbers or phone types for a person.

 

[HiTechCoach]

For the call tracking, that is good model when you have only one entity (e.g., everyone called "contacts"). I have four.

You have four types of entities, but they still should all be in a single entity table with a field for the type. In my opinion, you shouldn't have four separate tables.

 

[boblarson]

I concur with Boyd. Your 4 tables should be one.

 

[rainman89]

I agree with coach (And now Bob) . Create one contact table then a contact type table that has

businessman
freelancer
employee
frontdesk

etc.. in it. That would be a more "Normalized" approach

 

[foxtrot123]

I agree with coach (And now Bob) . Create one contact table then a contact type table that has

...
freelancer
employee
frontdesk

etc.. in it. That would be a more "Normalized" approach

Hmm ... then how would I model the fact that the employees and frontdesk people are children of businesses (i.e., one business can have many employees, and many frontdesk people), but freelancers don't have a parent? Maybe add to tblContacts a FK (BusinessID) to tblBusinesses, and include an "Unaffiliated" business type?

Thanks.

 

[rainman89]

Hmm ... then how would I model the fact that the employees and frontdesk people are children of businesses (i.e., one business can have many employees, and many frontdesk people), but freelancers don't have a parent? Maybe add to tblContacts a FK (BusinessID) to tblBusinesses, and include an "Unaffiliated" business type?

Thanks.

Yea that is a good way of doing it. You need to look at how you want this setup and what you will want to be able to pull from it in the end.

 

[HiTechCoach]

Hmm ... then how would I model the fact that the employees and frontdesk people are children of businesses (i.e., one business can have many employees, and many frontdesk people),

Use a junction table to create "self joins" between records.

This allow a many to many relationship between records.

... but freelancers don't have a parent? Maybe add to tblContacts a FK (BusinessID) to tblBusinesses, and include an "Unaffiliated" business type?

Using a junction table as recommended above, they would not have any relationships to other records. This would incicarte "Unaffiliated". You may not need to duplicate this with another business type.

 

[gemma-the-husky]

the thought was that you may still find it easier to take an existing template like the MS one, and tweak it to what you want - than start from scratch.