Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2007

    Help with database design issue

    Hello All,

    I am working on a company service and billing database.

    The database has the following tables:
    Customers(CustomerID, Account, Name, ...)
    Contacts(ContactID, CustomerID, Name, ...)
    SendPaths(SendPathID, ContactID, PathTypeID, LookupID, ...)
    SendPathTypes(PathTypeID, PathType) -> Phone, Fax, Email, Mail
    Addresses(AddressID, CustomerID, Address1, ...)
    PhoneFaxNumbers(PhoneID, Type, PhoneNumber)
    EMails(EmailID, Address)

    A Company can have many Contacts, a contact can have many PhoneNumbers, Emails, Mailing addresses.

    Problem: I do not understand how to relate these tables together.
    At first I tried using 3 seperate fields in SendPaths (AddressID, PhoneID, EMailID). But I only wanted to use 1 destination for each send path. For example, if the SendPath type is Mail, an invoice would be printed and mailed. A 2nd SendPath for that contact would be to email the invoice to 1 of several possible email addresses. A 3rd SendPath for that contact would be to fax the invoice. With 3 fields in each SendPath only 1 would be used for any given destination (like email).

    The second approach I tried was to use a PathTypeID and a generic LookUpID for each SendPath. This is the approach the attached diagram shows. I do not know how to make this solution work using stored proc.

    I would like to create a stored proc with parameters like CustomerID, ContactID that would return all the Sendpaths for that Contact.

    I am willing to completely start over if needed if anyone has a good approach to solving this problem.

    Any help on this would be greatly appreciated!

    Thank you
    Attached Thumbnails Attached Thumbnails Customers-Relationships.JPG  

  2. #2
    Join Date
    Jun 2007
    This question was answered
    08-18-10, 10:33

Posting Permissions

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