UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Numbers Or Text?, Access 2016    
 
   
femalegiraffe
post Feb 25 2018, 10:09 PM
Post#1



Posts: 56
Joined: 20-February 18



Greetings, I've been Googling and re-reading textbooks, searching for the answer to the above without success. Maybe it's such a basic issue no one but me needs it explained.

In the relationships diagram for Northwind sample database, I see a table Suppliers, where the first column is ID, and that's the primary Key, followed by Company, which is the company name, written out in text.

In the Purchase Orders table, the supplier is referenced by the supplier ID, rather than by the company name.

Would there be a reason for this other than, a foreign key thing? It seems to me:
Using ID to refer to a value, rather than using a name, allows for a simpler relationship between the two table. Which enables better querying.
Data entry of IDs rather than names reduces the potential for typos.

Maybe there are other advantages as well?

I ask because, (a) I'm new to this (b) I may find myself designing an Access database for users who (surprise) don't know databases. I foresee being asked, "Why use numbers? If you type the name instead of a number, then users see it and know immediately who you're talking about. Instead of having to check another table." (I know about designing forms and reports that will show names and be easy to read.)

So I want to have my answer ready . . . I hope this is clear and that I've used the correct words for what I'm trying to say.

Thanks in advance for any help.
Go to the top of the page
 
projecttoday
post Feb 25 2018, 10:14 PM
Post#2


UtterAccess VIP
Posts: 10,068
Joined: 10-February 04
From: South Charleston, WV


The thing is you don't need an answer because there will be no question. You see, when you develop a form for your users, they will select the name from a drop down list and Access will insert the number in the table automatically. The users will never see the ID number. So they will not question it.

--------------------
Robert Crouser
Go to the top of the page
 
nvogel
post Feb 26 2018, 02:17 AM
Post#3



Posts: 861
Joined: 26-January 14
From: London, UK


Some good criteria for choosing and defining keys are Simplicity, Stability and Familiarity.

The keys will become part of the business process so you do have to understand what keys will meet users' requirements. Find out what order numbers they use and how they identify suppliers today. Names sometimes make good keys but not always. Alphanumeric strings are common as identifiers. Numbers are less user-friendly but are common for certain things.


This post has been edited by nvogel: Feb 26 2018, 02:24 AM
Go to the top of the page
 
femalegiraffe
post Feb 26 2018, 05:44 PM
Post#4



Posts: 56
Joined: 20-February 18



Okay . . . that is something to hope for. I always assume things will arise that I don't want to arise but maybe with a little forethought this one won't. Thanks.
Go to the top of the page
 
projecttoday
post Feb 26 2018, 10:06 PM
Post#5


UtterAccess VIP
Posts: 10,068
Joined: 10-February 04
From: South Charleston, WV


You're welcome.

--------------------
Robert Crouser
Go to the top of the page
 
doctor9
post Feb 27 2018, 09:35 AM
Post#6


Remembered
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


femalegiraffe,

Keep in mind that your goal is to basically store the NAME of a supplier in only one place in your whole database. Everywhere else you just refer to Acme Industrial Propulsion Products at 123 Buttermarket Street in Dallas, Texas with just a long integer number. One of the benefits of doing things this way is if (for example) they move their corporate headquarters to 729 Brookfield Lane in Albuquerque, New Mexico you just change the address, city and state in ONE place, and that change can immediately be seen on all of their invoices and envelopes and past due notices that you print from that point on. The long integer foreign key doesn't change in any of your tables.

If you're concerned about the users working directly with your tables (which they should NEVER be able to do), you might want to consider distributing a compiled frontend where they can't open the Navigation Pane or open forms in design view or view your VBA code. That stuff is like the wiring behind the car's dashboard; you don't want them to mess with it.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    25th September 2018 - 02:05 AM