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

This question was answered
08-18-10, 10:33
carlosn