Results 1 to 4 of 4

Thread: Design question

  1. #1
    Join Date
    Apr 2002
    Posts
    25

    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]
    Last edited by perryjm; 02-18-03 at 12:22.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Design question

    It would be a pity to have a table for each - duplication of effort, and you cannot easily query across all calls (should you wish to).

    There are probably other things these entities have in common - address, phone number, etc.

    A common solution is to introduce a "supertype" entity - often called Party to pull together the common information:

    Party( PartyID PK, Type, Name, Phone, Address, ... )
    Customer( PartyID PK/FK, CustomerRef (Unique)... )
    Employee( PartyID PK/FK, EmpID (Unique), Salary, Deptno, ... )
    ...
    CallHistory( CallID PK, PartyID FK, ... )

    This means that when you create a Customer, Employee etc. you must first create a Party of that type, then the actual Customer/Employee/??? record.

    Alternatively you may prefer to displense with the subtype tables and just have:

    Party( PartyID PK, Type, Name, Phone, Address, CustomerRef(Unique), EmpID(Unique), Salary, Deptno... )
    CallHistory( CallID PK, PartyID FK, ... )

    i.e. Party has all the columns for all the subtypes. This is a trade-off between efficiency and clarity.

  3. #3
    Join Date
    Apr 2002
    Posts
    25

    Thanks!

    Thank you, I am in the process of rebuilding our current database and I want to make sure that I do it well. The super-entity makes sense. The actual entities in my system are not customers and suppliers, but:
    StudentProspects
    High Schools
    Colleges
    Churches
    Businesses
    and
    Affiliates

    StudentProspects are the primary entity (some 50,000 records), so I may keep them seperate and combine everything else into a super-entity called "Organization"

    Does this sound like a good plan?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Thanks!

    Sounds very reasonable! You can either have 2 CallHistory tables, or one with an "arc" relationship to Student or Organisation - i.e. it has a FK to Student and a FK to Organisation, but each record can only have one or the other (check constraint).

Posting Permissions

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