First of all, I'm sure someone must have posted an answer to this question somewhere, I just can't find it. Please point me in the right direction!
My question revolves setting up relationships for some contact tables. I would like to completely normalize some contact tables that are to contain data for the following:
- companies
- contacts
- addresses
- phone/fax numbers
- email addresses
- Bid documents
Relationships may be set up like this:
- One company may have many or no addresses, many or no contacts, many or no phone/fax numbers, and one or no email addresses.
- One contact may have many or no companies (none if it's an individual rather than represented by a company, many if they're a rep for several companies), many or no addresses, many or no phone numbers, many or no email addresses.
- One address may have one or no company, none or many contacts, many or no phone numbers.
- A bid document is obtained by zero or more companies, zero or more contacts from the same company may request it, and it is sent to one or more addresses.
Other databases I've seen either use
A) a flat file approach
B) allows a relationship that only starts from the company standpoint. So, one company can have many addresses, each address has many contacts, and if one contact is related to several addresses, you have to re-enter the contact for each address. The phone numbers for the contact and company are handled in a flat file method, so there's a column for the company phone, company fax, contact phone; you have to re-enter the contact name to show the contact cell number. Also, if you are listing an individual instead of a contact, you list the individual's name in the company name field, rather than in the contact name field.
I'd like to be able to have the ability to have a company linked to many addresses and then for each address to show many contacts, then allow each contact to have multiple addresses and phone/fax and email information; however, I don't want to have to repeat the data for each address listing. I also want to be able to view the data from the contact point of view, in case it's an individual rather than a contact.
I hope that makes sense! Please let me know if I can clarify my question(s).
Also, if anyone knows of a sample form or database application that uses a normalized structure for its contact information, please let me know.
Thanks for any help you can provide!
My question revolves setting up relationships for some contact tables. I would like to completely normalize some contact tables that are to contain data for the following:
- companies
- contacts
- addresses
- phone/fax numbers
- email addresses
- Bid documents
Relationships may be set up like this:
- One company may have many or no addresses, many or no contacts, many or no phone/fax numbers, and one or no email addresses.
- One contact may have many or no companies (none if it's an individual rather than represented by a company, many if they're a rep for several companies), many or no addresses, many or no phone numbers, many or no email addresses.
- One address may have one or no company, none or many contacts, many or no phone numbers.
- A bid document is obtained by zero or more companies, zero or more contacts from the same company may request it, and it is sent to one or more addresses.
Other databases I've seen either use
A) a flat file approach
B) allows a relationship that only starts from the company standpoint. So, one company can have many addresses, each address has many contacts, and if one contact is related to several addresses, you have to re-enter the contact for each address. The phone numbers for the contact and company are handled in a flat file method, so there's a column for the company phone, company fax, contact phone; you have to re-enter the contact name to show the contact cell number. Also, if you are listing an individual instead of a contact, you list the individual's name in the company name field, rather than in the contact name field.
I'd like to be able to have the ability to have a company linked to many addresses and then for each address to show many contacts, then allow each contact to have multiple addresses and phone/fax and email information; however, I don't want to have to repeat the data for each address listing. I also want to be able to view the data from the contact point of view, in case it's an individual rather than a contact.
I hope that makes sense! Please let me know if I can clarify my question(s).
Also, if anyone knows of a sample form or database application that uses a normalized structure for its contact information, please let me know.
Thanks for any help you can provide!
