Go Back   Access World Forums > Microsoft Access Discussion > Theory and practice of database design

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-27-2019, 04:39 AM   #1
Craig6
Newly Registered User
 
Join Date: Jun 2019
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Craig6 is on a distinguished road
Design method

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

Craig6 is offline   Reply With Quote
Old 06-27-2019, 04:54 AM   #2
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,280
Thanks: 528
Thanked 928 Times in 879 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Design method

Quote:
Originally Posted by Craig6 View Post
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.
__________________
Code:
                 |||||
               @(~‘^‘~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 06-27-2019, 09:23 AM   #3
Craig6
Newly Registered User
 
Join Date: Jun 2019
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Craig6 is on a distinguished road
Re: Design method

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

Craig6 is offline   Reply With Quote
Old 06-27-2019, 04:36 PM   #4
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 942
Thanks: 10
Thanked 195 Times in 185 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Design method

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.
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Old 06-28-2019, 12:32 AM   #5
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,267
Thanks: 40
Thanked 3,651 Times in 3,521 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Design method

you may need more tables still - perhaps the contact has more than one telephone number? perhaps the customer has more than one address?
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 06-28-2019, 01:15 AM   #6
Craig6
Newly Registered User
 
Join Date: Jun 2019
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Craig6 is on a distinguished road
Re: Design method

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?
Craig6 is offline   Reply With Quote
Old 06-28-2019, 03:36 AM   #7
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,280
Thanks: 528
Thanked 928 Times in 879 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Design method

Quote:
Originally Posted by Craig6 View Post
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 99 Cents is actually available for free if you go to:- https://sellfy.com/p/l24j/ and enter the coupon code:- IHaveSubscribedToYourYouTube

__________________
Code:
                 |||||
               @(~‘^‘~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)

Last edited by Uncle Gizmo; 06-28-2019 at 03:44 AM. Reason: Added Quote
Uncle Gizmo is offline   Reply With Quote
Old 06-28-2019, 06:51 AM   #8
AccessBlaster
.
 
Join Date: May 2010
Posts: 1,156
Thanks: 27
Thanked 271 Times in 258 Posts
AccessBlaster has a spectacular aura about AccessBlaster has a spectacular aura about
Re: Design method

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.

Attached Images
File Type: jpg Capture.jpg (55.7 KB, 117 views)
AccessBlaster is online now   Reply With Quote
Old 07-01-2019, 08:53 AM   #9
RogerCooper
Newly Registered User
 
Join Date: Jul 2014
Posts: 161
Thanks: 5
Thanked 11 Times in 10 Posts
RogerCooper is on a distinguished road
Re: Design method

Quote:
Originally Posted by Craig6 View Post
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.

RogerCooper is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Method - TRANSFERSPREADSHEET is a hidden method Bilbo_Baggins_Esq Modules & VBA 2 08-14-2013 11:21 AM
Database design question; Limit records via table design or a query or both TimTDP Theory and practice of database design 3 06-05-2013 12:35 PM
form design, query design, db design verdes Forms 3 01-30-2013 07:55 PM
Cause of 'object can't support this method' error using find method? Margarita Modules & VBA 2 04-11-2012 04:51 AM
What's the best method to design a form to filter records? jonathanchye Forms 0 03-15-2011 02:21 AM




All times are GMT -8. The time now is 11:11 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World