Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 347
  • Last Modified:

Contacts table data design

Hi,

I have been working on a Contact Management programme for a few months now to use at my work.  I have been asking my employees how they currently do things so that I can make sure that the software i develop compliments what they are already doing and hopefully makes it easier.

My contact table (tblContacts) has:
ID autonumber
company_name
contact_title
contact_fname
contact_lname
address1
address2
Postalcode
etc...

It has been mentioned to me that some companies have more than one contact associated with them.  What would be the best way to do this?  Would it be a separate contacts table (tblContacts) or is there a better way?

I envisage the main contacts form having some sort of button below the two fileds contact_fname and contact_lname with 'Add another Contact@ or something so that the user could select this and add more.

Just a thought.

I appreciate your suggestions.

Regards,
Tom
0
d10u4v
Asked:
d10u4v
3 Solutions
 
Rey Obrero (Capricorn1)Commented:
create a table for companies, set as the row source for the Company_name combo box in your form, such that when adding new contacts, company name will be consistent.
0
 
Helen FeddemaCommented:
And set up a one-to-many relationship between tblCompanies and tblContacts, in the Relationships diagram.  You may also need one or more tables of IDs, addresses, and/or phone numbers, since typically a contact has a number of these.  Link them to tblContacts (one-to-many from tblContacts to tblContactIDs, etc.)  The sample database for my Access 2007 book is set up like this.
In your tblContacts, you should not store the company name, but a CompanyID (generally an AutoNumber field in tblCompany).  You can get the company name though the link, when needed, and the combo box for selecting the company can display the company name, but store the ID in tblContacts.
0
 
d10u4vAuthor Commented:
Hi,

Thanks for the advice.  Does the following tables look ok:

tblContacts
contactID PK
companyID FK
status_code FK
email_address
website
contact_lname
contact_fname
job_title
department
work_phone
mobile_phone
fax_number

tblCompanies
companyID PK
company_name
address_id FK

tblAddresses
addressID PK
address_line1
address_line2
address_line3
postal_code
county

Do you think this structure will work?  I have included a few screenshots of the imput screen so as to give an example of what i'm working with.  i have added a tab on the contacts fields so that a user can see or add any additional contacts to a company.  I understand that most of the changes are behind the scenes changes and don't really affect the GUI.

Regards,
Tom
CP1.jpg
CP2.jpg
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
d10u4vAuthor Commented:
Hi,

Just an after thought.   I have been looking at other database templates to see how other are managing the addresses.  Microsoft seem to keep all the address data in on table along with the company name.  What is the advantage of splitting this down into tblCompany and tblAddresses?

Tom
0
 
d10u4vAuthor Commented:
Here is my relationships layout
relationships.jpg
0
 
harfangCommented:
Are you sure you need several addresses for the same company? As you said, it's easier to manage them directly in the company table. If you don't, you will probably also need to link the contacts to one of the company addresses, which is a pain.

Side note: using three address lines came out of fashion many years ago. Instead, use a single large field and allow the users to enter several lines in the text box.

(°v°)
0
 
d10u4vAuthor Commented:
Thanks for giving me something to think about, especially the 3 lines of address to large field.
0
 
Helen FeddemaCommented:
Microsoft sample databases often have a denormalized structure for compability with the flat-file MAPI database used by Outlook.  However, this is not necessary.  It takes some coding, but in the sample database for my Access 2007 book, I synchronize linked Access tables to Outlook contacts.  

Your database structure looks good, but I think you probably need to add some fields for phone numbers, emails and Websites to the Company table, since they may be different from the contact values.

If you are sure you will never have to add multiple addresses for a company, you don't need a separate linked table.  But if there is any possibility, better make the linked table, because it would be a pain to have to make the change later, after adding many records to the Company table.

It is actually more likely that the contacts will have multiple addresses than the companies -- say Home and Work.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now