?
Solved

Contacts table data design

Posted on 2010-01-09
8
Medium Priority
?
336 Views
Last Modified: 2013-11-29
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
Comment
Question by:d10u4v
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 664 total points
ID: 26273053
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
 
LVL 31

Accepted Solution

by:
Helen Feddema earned 668 total points
ID: 26274065
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
 

Author Comment

by:d10u4v
ID: 26277235
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:d10u4v
ID: 26277442
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
 

Author Comment

by:d10u4v
ID: 26277575
Here is my relationships layout
relationships.jpg
0
 
LVL 58

Assisted Solution

by:harfang
harfang earned 668 total points
ID: 26318491
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
 

Author Closing Comment

by:d10u4v
ID: 31674973
Thanks for giving me something to think about, especially the 3 lines of address to large field.
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 26335469
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question