Link to home
Start Free TrialLog in
Avatar of kittenwhisky
kittenwhiskyFlag for United States of America

asked on

Database design - best approach

I need to design a database containing call logs to customers and am looking for general help on how to best design it.

First of all 2 things, I need to keep track all previous calls made to customers, and also keep track of future calls that are due to be made to the customer.

There are 3 types of call:

1) maintenance call : each client has a call frequency given their priority, eg. once a month. a general service call needs to be made within the month based starting from date on which last call was made to client (any call type counts as last call)
2) follow up call: any call type can generate a new call to be made. the follow up call will thus be linked to a previous call
3) task call: a task can involve one or more clients. each client needs to be called regarding that task.

incomplete call feature
Call type (regular, follow up, or task)
Entry Date (date call was entered into schedule)
Due Date (date by which call needs to be completed by)
Description (description of call purpose)

additional features after call completed:
Call summary (summary of conversation)
Completion Date (date on which call was actually completed)

Additional points to take into account:
1) A maintenance call is a recurring outstanding call for each customer. Any call types counts towards a last call. When a call is completed, a maintenance call due date is scheduled, as the last call date has been updated.
2) A task call may involve several customers. Eg a task could be to inform all high priority customers of a new high priority product. this task should generate an outstanding call to be made for each customer.
3) follow up calls should store some link to the call that generated the follow up call (eg the task call example described above generates a request from customer to receive more information about a new product, this should generate a follow up call, and a link to the initial task call should be stored somewhere);

So now, how to design it? Have to two tables, one for outstanding calls, one for completed calls? Have 3 tables, one for each call type? When a call is completed, all previous maintenance call schedules should be reset to take into account the new call date. how to handle this ? How to handle reference between calls (eg task call & follow-up call)? How to handle tasks involving multiple clients ? (eg generate one task ID, have a join table linking task ID to customer ID?)

I will implement this into MS Access 2003.
Any inputs/ideas/examples would be greatly appreciated.


ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial