Jan 29 2007, 05:52 AM
I wonder if anybody can help with this problem.
I am setting up a basic contact list, with 3 tables - contact, address and company.
What I want to do is be able to add numerous addresses to the company table/form. Then, when I add a contact, I can go to the contact form and select an existing address from a drop down (from the address table), this will then assign this address to the contact, as well as showing this contacts name on company screen.
I have got this working as far as having a drop down of company names on the contacts screen but I can't work out how I can get the address to show on the contact screen when I select a company name from a drop down list?
I have spent ages on this, and its driving me mad, please can somebody hep?
I think i might need to sort out the relationships, but am getting very confused.
Jan 29 2007, 06:28 AM
First, you are going to need a lot more tables to do this correctly. Since you want to add more the one address for each company and each company can have more the one contact (this part I'm assuming) you will need several junction tables, etc.... I would recommend something on the lines of the following:
ContactID - Primary Key - autonumber
CompanyID - PK - Autonumber
ContactCompanyID - PK - Autonumber
ContactID - Foreign Key
CompanyID - FK
StateID - PK - Autonumber
CityID - PK -AutoNumber
ZipCodeID - PK - AutoNumber
CityID - FK
StateID - FK
ZipID - FK
CompanyAddressID - PK - AutoNumber
CompanyID - FK
AddressID - FK
Jan 29 2007, 08:05 AM
While this structure adheres to the Normal Forms, addresses is one area where I generally opt for convenience for the end user rather than strict adherence to the Forms. It becomes very cumbersome to have to enter an address before you can use that address. As a rule, addresses are usually limited to only a single entity (not necessarily the case when dealing with a single street adress for appartments or suites but the trade off is usually not that significant).
Just my $0.02.
Jan 29 2007, 09:41 AM
Jerry, I completly agree. In fact, my "standard" address tables are as follows:
I know that to be fully normalized, it should be split down further for cities and states, but convenience makes is much more user friendly. When posting a response here however, I tend to taken the normalization to the nth degree where possible so the Normalization Nazis don't come after me.
Jan 29 2007, 10:01 AM
LOL, you've taken addresses farther than I would. I tend to just leave them in the table with the entity. But I concur with the zip table.
Jan 29 2007, 10:12 AM
The only time I break out addresses in a different table is if I have to store multiple addresses for the same person/company, like a mailing address and a shipping address.
Jan 31 2007, 11:32 AM
Thanks for the info.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here