Basic table structure question

I know this is a really basic question, but I am really struggling to get my
head round it, so perhaps someone can help me.

All the books I have read and online training sessions I have done seem to
recommend using an ID number for tables such as company names/ details or
contact details. I can't quite understand why it is beneficial to use an ID
number rather than the actual name of the company because when you create a
relationship between two tables you only see a set of numbers which don't
mean anything.

Can anyone explain it to me in simple terms - sorry if this the dumbest
question ever posted!

Well, let's say you have four tables...

Company data
Company Receivables
Company Orders
Company Contact


Let's say that one of the companies you deal with is called Universal Data
Processing Corporation DBA UDP, LTC.

Every time you go to add a record to the Company Contact table, do you
really want to type that, or would you rather type thier customer number
(1234)?



One other point, you state, "when you create a relationship between two
tables you only see a set of numbers which don't mean anything". That is
not true. If you use lookups and relationships properly, you will type in
the number (1234) but what you will see in your queries, reports, forms,
etc. is the actual company name.

Look at the Northwinds sample database and study it's structure.

Hope that helps a bit.

Rick b



"Mattymoo" wrote in message
...
I know this is a really basic question, but I am really struggling to get
my
head round it, so perhaps someone can help me.

All the books I have read and online training sessions I have done seem to
recommend using an ID number for tables such as company names/ details or
contact details. I can't quite understand why it is beneficial to use an
ID
number rather than the actual name of the company because when you create
a
relationship between two tables you only see a set of numbers which don't
mean anything.

Can anyone explain it to me in simple terms - sorry if this the dumbest
question ever posted!

Rick

Thanks for your reply

If I had to add Universal Data Processing Corporation DBA UDP, LTC. to my
company contact table, wouldn't I input it through a look up table? So for
example I have a list of lead sources (newspaper, Email, Website etc) which
is linked into my clientdetails table via a look up table - should I have
used an autonumber for those?

I've been looking at the sample databases that come with 'Access 2003 Inside
Out' and that's when I started to get confused


"Rick B" wrote:

Well, let's say you have four tables...

Company data
Company Receivables
Company Orders
Company Contact


Let's say that one of the companies you deal with is called Universal Data
Processing Corporation DBA UDP, LTC.

Every time you go to add a record to the Company Contact table, do you
really want to type that, or would you rather type thier customer number
(1234)?



One other point, you state, "when you create a relationship between two
tables you only see a set of numbers which don't mean anything". That is
not true. If you use lookups and relationships properly, you will type in
the number (1234) but what you will see in your queries, reports, forms,
etc. is the actual company name.

Look at the Northwinds sample database and study it's structure.

Hope that helps a bit.

Rick b



"Mattymoo" wrote in message
...
I know this is a really basic question, but I am really struggling to get
my
head round it, so perhaps someone can help me.

All the books I have read and online training sessions I have done seem to
recommend using an ID number for tables such as company names/ details or
contact details. I can't quite understand why it is beneficial to use an
ID
number rather than the actual name of the company because when you create
a
relationship between two tables you only see a set of numbers which don't
mean anything.

Can anyone explain it to me in simple terms - sorry if this the dumbest
question ever posted!

You posted in the right place. There are no dumb questions, but there is
some really lame database design out there, done by people who didn't ask.
Each record should have a unique identifier, or Primary Key (PK). If the
company name is the unique identifier, what happens if the company is
absorbed by another one or changes its name? Think of social security
numbers, which remain the same through any name changes. You can change your
name a dozen times, but your account information is always associated with
you through your SS#.
If you have a Customer table, and each customer places orders for your
company's product, then each customer will have many associated orders. Each
order, however, will have just one customer. Therefore the relationship of
Customers to Orders is one-to-many. The Customer table (tblCustomer) needs a
PK (call it CustomerID), which is by definition on the "one" side of the
relationship. The Orders table contains its own PK, and also a foreign key
(FK).
There has been some disagreement about the use of autonumbers (automatically
assigned unique number) for the PK field, but for now at least go ahead and
add an autonumber PK field in table design view. It looks something like
this:

tblCustomer
CustomerID (PK)
Name
Address
etc.

tblOrders
OrderID (PK)
CustomerID (FK)
Item
Quantity
etc.

Click Tools Relationships. Add both tables, then drag CustomerID from
tblCustomers to on top of CustomerID in tblOrders. Click Enforce Referential
Integrity, then Create. Note that CustomerID in tblOrders needs to be of the
data type Number. Also note that while you need to define PK in table design
view, the FK is defined only by its relationship to the PK. You don't
designate it a FK field as you do the PK field.
Open tblCustomer and add customer info. After that is done you should see a
+ sign at the left side of the table. Click it to add orders for that
customer. Repeat with another customer. Now look at tblOrders to see how
CustomerID is now part of the order records. It will remain so no matter
what happens to the customer name over time.
Use autoform to get a quick look at how forms work. Use the combo box
wizard to see how you can look up the customer by name rather than by ID.
Experiment, then post back with specific questions. Do not be in a hurry to
deply your first database, if possible. Good luck.

"Mattymoo" wrote:

I know this is a really basic question, but I am really struggling to get my
head round it, so perhaps someone can help me.

All the books I have read and online training sessions I have done seem to
recommend using an ID number for tables such as company names/ details or
contact details. I can't quite understand why it is beneficial to use an ID
number rather than the actual name of the company because when you create a
relationship between two tables you only see a set of numbers which don't
mean anything.

Can anyone explain it to me in simple terms - sorry if this the dumbest
question ever posted!

Thank you very much - putting it into context like that is just what I needed.

"Bruce" wrote:

You posted in the right place. There are no dumb questions, but there is
some really lame database design out there, done by people who didn't ask.
Each record should have a unique identifier, or Primary Key (PK). If the
company name is the unique identifier, what happens if the company is
absorbed by another one or changes its name? Think of social security
numbers, which remain the same through any name changes. You can change your
name a dozen times, but your account information is always associated with
you through your SS#.
If you have a Customer table, and each customer places orders for your
company's product, then each customer will have many associated orders. Each
order, however, will have just one customer. Therefore the relationship of
Customers to Orders is one-to-many. The Customer table (tblCustomer) needs a
PK (call it CustomerID), which is by definition on the "one" side of the
relationship. The Orders table contains its own PK, and also a foreign key
(FK).
There has been some disagreement about the use of autonumbers (automatically
assigned unique number) for the PK field, but for now at least go ahead and
add an autonumber PK field in table design view. It looks something like
this:

tblCustomer
CustomerID (PK)
Name
Address
etc.

tblOrders
OrderID (PK)
CustomerID (FK)
Item
Quantity
etc.

Click Tools Relationships. Add both tables, then drag CustomerID from
tblCustomers to on top of CustomerID in tblOrders. Click Enforce Referential
Integrity, then Create. Note that CustomerID in tblOrders needs to be of the
data type Number. Also note that while you need to define PK in table design
view, the FK is defined only by its relationship to the PK. You don't
designate it a FK field as you do the PK field.
Open tblCustomer and add customer info. After that is done you should see a
+ sign at the left side of the table. Click it to add orders for that
customer. Repeat with another customer. Now look at tblOrders to see how
CustomerID is now part of the order records. It will remain so no matter
what happens to the customer name over time.
Use autoform to get a quick look at how forms work. Use the combo box
wizard to see how you can look up the customer by name rather than by ID.
Experiment, then post back with specific questions. Do not be in a hurry to
deply your first database, if possible. Good luck.

"Mattymoo" wrote:

I know this is a really basic question, but I am really struggling to get my
head round it, so perhaps someone can help me.

All the books I have read and online training sessions I have done seem to
recommend using an ID number for tables such as company names/ details or
contact details. I can't quite understand why it is beneficial to use an ID
number rather than the actual name of the company because when you create a
relationship between two tables you only see a set of numbers which don't
mean anything.

Can anyone explain it to me in simple terms - sorry if this the dumbest
question ever posted!

Mattymoo wrote:

I know this is a really basic question, but I am really struggling to get my
head round it, so perhaps someone can help me.

All the books I have read and online training sessions I have done seem to
recommend using an ID number for tables such as company names/ details or
contact details. I can't quite understand why it is beneficial to use an ID
number rather than the actual name of the company because when you create a
relationship between two tables you only see a set of numbers which don't
mean anything.

Can anyone explain it to me in simple terms - sorry if this the dumbest
question ever posted!

Mattymoo, company names are not unique. Google on something relatively
simple - say, ABC Plumbing - and see how many hits you get.

Second, primary keys are used to uniquely identify individual records,
whether they are seen by the user or not. No-one has to "see"
meaningless numbers if you don't want them to. It's all about how you
set up your queries, forms, & reports. This is why for a multi-user
database, it's best to use forms for data entry...the user sees only the
human-meaningful info.

Third, as someone else has already pointed out, it's easier to join
tables on ID numbers than on company names, especially when the
possibility exists for a user to type in a company name incorrectly.
This goes back to using forms for data entry. The user selects "ABC
Plumbing and Electrical Contractors" in the form, but Access inserts the
ID number into the data record. This precludes the possibility of
someone misspelling a word or otherwise miskeying. This is what the
relational model is all about...The company names are in the database
ONCE, in the CompanyName field of tblCompanies, & related tables are
joined to tblCompanies on the ID, NOT on company name. All you have to
do anytime you want to see (or want users to see) the CompanyName is to
add tblCompanies to the query (or the query underlying the form or
report) & include the CompanyName field rather than the ID field.

Does this help?

LeAnne

Ok, reading the replies there is one thing that I didn't see mentioned, but I
am not sure if it is true. Wouldn't there also be a space savings if you are
storing a 4 byte long integer instead of a 50 character name at 1 to 2 bytes
per character?

"Mattymoo" wrote:

I know this is a really basic question, but I am really struggling to get my
head round it, so perhaps someone can help me.

All the books I have read and online training sessions I have done seem to
recommend using an ID number for tables such as company names/ details or
contact details. I can't quite understand why it is beneficial to use an ID
number rather than the actual name of the company because when you create a
relationship between two tables you only see a set of numbers which don't
mean anything.

Can anyone explain it to me in simple terms - sorry if this the dumbest
question ever posted!