Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2002
    Posts
    25

    Unanswered: Design question:

    Design question
    I want to keep a history of phonecalls made to different entities within my database.

    MS Access database... I have tables with customers, suppliers, customer employees, supplier employees, and organizations.

    My question then is this:

    Do I create a CallHistory table for each different entity or do I create 1 CallHistory table that holds all calls made?

    Fields in my CallHistory table will include:

    [CallID] (autonumber primary key), [Date Done], [To Do Date], [InitiatedBy], [fk_ContactMethod] (phonecall, letter, e-mail, voicemail), and [Comments]

  2. #2
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    32

    Re: Design question:

    Well, it depends on some facts. Here are my thoughts.

    [list=1]
    I think you will call only people, not organizations. Perhaps you need a CallHistory table for customer employees and supplier employees only.

    Where is the relation between entity table(s) and CallHistory table stored? There is no foreign key in your CallHistory table? You need a foreign key for the entity. Note that every field in the entity tables must have the same data type as the foreign key field.

    If the foreign key is not important you need only one CallHistory table. But I doubt that it is unimportant to know, with whom you made a phonecall.

    It's also a question of performance. How many records will be stored in the CallHistory table? And how often do you select/insert/update the data? To increase the performance it might be a good solution to create a CallHistory table for each entity.[/list=1]

    Peter

  3. #3
    Join Date
    Jan 2003
    Posts
    46
    Peter,

    You haven't said how (or where) you are storing phone numbers. And that will impact how you will design your phone call log.

    Do you have a single table for all phone numbers or are they seperately stored for organizations, customers, etc.? Or do you intend for the data entry people to just enter phone numbers each time they make a call (not a good approach).

    It appears you may be running against the normalization grain with seperate tables for each type of entity. Whether this will cost you down the road depends on where you go with the application and what will be done with the data.

    As for the number of records - it shouldn't be a concern. Old phone log records are usually not used on a daily basis and hence easily and agreeably archived, so when your table begins accumulating more than a few hundred thousand records, you can consider some options.

    Let us know a bit more about your table design.

    Cheers
    zambezibill

  4. #4
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    32
    Hi zambezibill,

    I agree. It is important how phone numbers will be stored. But that's a question to perryjm and his table design.

    The problem is also discussed in:
    dBforums General Database Concepts & Design Design question

    Peter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •