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)
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.