Design method (2 Viewers)

Craig6

Registered User.
Local time
Today, 09:07
Joined
Jun 20, 2019
Messages
33
Hello, Is it prudent to have two tables when building a customer database, one for contacts and one for company names.

I have a customer table which is based on company names and has fields for contacts. However, there may be many contact names for each company. So I'm thinking I should create 2 tables,one where my contacts live and the form would then have a combobox for the company name linked to the customer table.

Or, is it best to stick with one table (as i've got) and have a few more fields showing contact1, contact2 etc

Thanks
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 09:07
Joined
Jul 9, 2003
Messages
16,245
Hello, Is it prudent to have two tables when building a customer database, one for contacts and one for company names.

Yes definitely, there are exceptions but they would be red Herrings I reckon.

One of the most common problems for people starting out with MS Access is that they often import an Excel spreadsheet and then they run into some difficulties. I discuss this on my website here:- Excel in Access

although that's not specific to your question, I do show some examples of creating subforms to display related data.

In other words the company table will need to relate to the contact table. (Assuming that you mean that your contacts are related to companies) It might be worth a read through.
 

Craig6

Registered User.
Local time
Today, 09:07
Joined
Jun 20, 2019
Messages
33
Yes, my customer table will have many contacts in the contact table and will be related. I've now created a contacts table and linked it to the customer table. Thanks
 

Micron

AWF VIP
Local time
Today, 05:07
Joined
Oct 20, 2018
Messages
3,476
You should consider this as pretty much a truth to go by: If you need to add another field to allow for the same kind of data, your design approach is flawed. It's what I call using your Excel brain. Not only can that make data retrieval difficult, it means having to add that field to every existing query, form, report and perhaps code as well. That's another sign of what's known as a lack of normalization.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:07
Joined
Feb 19, 2013
Messages
16,553
you may need more tables still - perhaps the contact has more than one telephone number? perhaps the customer has more than one address?
 

Craig6

Registered User.
Local time
Today, 09:07
Joined
Jun 20, 2019
Messages
33
I can see this growing and growing then. I suppose a contact will have more than one number but I could just create fields in the table called, Phone, Mobile, Home etc?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 09:07
Joined
Jul 9, 2003
Messages
16,245
I could just create fields in the table called, Phone, Mobile, Home etc?

It becomes an Issue
Well yes, you could do it like that and I suspect many do. It becomes an issue for two reasons, if you wanted to search phone numbers, and if you want to add an extra communication type. Let's say the phone rang, a number popped up and you wonder if that's so & so ringing. If you store numbers in multiple places, it makes the searching more of a challenge, it's much easier just to search one field.

Require Adding another Field
The other problem is, 20 years ago all you had was phone, fax and possibly one or two other communication related Fields. Now you have phone, mobile phone, email, WhatsApp, Skype, web pages, all sorts of mediums for making contact with people. And they are being added to all the time. Skype and WhatsApp being recent additions. So if you were storing your communication data in separate fields, then adding WhatsApp would require adding another field.

Add an Entry in a Combo Box
However if you develop your database properly, along the recommended guidelines, then you just add the new communication method to a list which appears in a combobox. One entry and you've completely redesigned your database. Adding an extra field doesn't sound to onerous particularly when you first set up your database it isn't, but imagine in six months or a year, your database will be much bigger and more complicated. It's very likely that you will have queries and reports based on the information in those communication Fields. Now when you add an extra field, you're going to have to change those queries and reports and any other parts of your database that use that information.

Video Instructions HERE:-
I have a set of videos on my website which demonstrate how to change a communication table, your telephone list table with individual fields for the different sorts of telephone numbers into the correct structure. See this link here:- (Parts 1, 2 and 3 as Video Instructions)

Free Transpose Tool Here:-
The transpose tool mentioned in the video and available for free if you go to:- https://gum.co/NiftyTransposeTool and enter the coupon code:- 95r2296
 
Last edited:

AccessBlaster

Registered User.
Local time
Today, 02:07
Joined
May 22, 2010
Messages
5,828
Craig,

The good news is you do not have to reinvent the wheel, you can look at existing Customer table designs and adapt it to your needs.

Not being an expert database builder, I have always relied on the older Microsoft Northwind database sample as my go to reference.
In the sample below, you can use the order details or the customer details or a combination. Make it simple or complex, its up to you.

HTH, good luck with your project.

 

Attachments

  • Capture.jpg
    Capture.jpg
    55.7 KB · Views: 286

RogerCooper

Registered User.
Local time
Today, 02:07
Joined
Jul 30, 2014
Messages
277
Hello, Is it prudent to have two tables when building a customer database, one for contacts and one for company names.

I have a customer table which is based on company names and has fields for contacts. However, there may be many contact names for each company. So I'm thinking I should create 2 tables,one where my contacts live and the form would then have a combobox for the company name linked to the customer table.

Thanks
Make sure to set up a 1-to-many relationship between customers and contacts. We have an ERP here that treats customers and contacts as having a many-to-many relationship, which makes it much harder to work with. Although it is possible to have the same contact at more than 1 customer, it is better to treat those exceptional situations as multiple contacts.
 

Users who are viewing this thread

Top Bottom