A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Understanding Primary Keys



 
 
Thread Tools Display Modes
  #1  
Old January 20th, 2005, 07:18 PM
Khai
external usenet poster
 
Posts: n/a
Default Understanding Primary Keys

table1
eID as long (autonumber)
miscinfo as text
moreinfo as text

table2
eID as long?
e2ID as long?
yetmoreinfo as text

Say I had multiple table2's for each table1. Would eID in table2 be set to
the same value in table1.eID, and then, would that mean e2ID would be a
unique key on it's own, labeled PK? Is this where Foreign Keys are? So
table1.eID and table2.eID equal each other?

I understand the reasoning for PK's - but having trouble understanding the
concept and how exactly they relate to another table. =/

in the example relationship dbase at
http://members.iinet.net.au/~allenbrowne/AppHuman.html (I found that in
another post here in the group), it has GroupTypeID listed in tblGroup and
tblGroupType. Say they're long autonumbers, do you defined tblGroupID as
Long (Autonumber), or just as Long, and on Creation, set it to
tblGroupType.GroupTypeID ? Then, if that were the case, how would you pull
up all tblGroups in a form that were listed to tblGroupType.GroupTypeID?

*wandering around in circles*
DPHarr, hopeful database creator.


  #2  
Old January 20th, 2005, 08:47 PM
LeAnne
external usenet poster
 
Posts: n/a
Default

Whoa there, young feller. Take a deep breath. Now take another. I think
you need to step out of the circle and consider some basic tenets of
relational databases.

First: Access creates Relationships when you join tables in the
Relationships window. It's up to you to develop & set up your db schema
in such a way that relationships between tables are clearly understood
by Access, and are therefore clearly defined (not of the dreaded
"indeterminate" type) when created. Access then uses the relationships
to find associated information in your db quickly and efficiently.

As with anything, you have to start at the beginning. What you
essentially have to do is chop your data down to their most atomic and
logical components (aka "decomposing"), then figure out how to put them
all back together again in meaningful ways. Ask yourself, what are the
entities (real-life persons, places, things, or events that the db will
keep track of) and their attributes (categories of related information
relevent to each entity)? Entities & attributes = tables & fields. Once
you have determined what tables and fields the db needs, then you have
to set your primary keys (using one or more fields) to uniquely identify
each record in a table.

Then once the pk's are in place, you have to tell Access how to bring
all the information together again using criteria that you will set when
you query the db (e.g. "All orders for fiscal year 1999", "All streams
in the Shenandoah River watershed", "All contributions by corporate
donors greater than $10,000," etc.). To set up relationships between
tables, add one table's pk to the other table (in which case it's known
as a foreign key). To decide which table's pk should go where, you have
to figure out how entities are related to each other:

- In a one-to-many relationship (1:m), a record in Table A can have more
than one matching record in Table B, but a record in Table B has no more
than one matching record in table A. To set up this relationship, add
the field(s) that make up the pk on the "one" side of the relationship
to the table on the "many" side. Check out the Northwinds example
database included with most versions of Access for an example of such a
relationship between the Suppliers and Products tables; one supplier can
supply more than one product, but each product has only one supplier.

- In a many-to-many (m:n) relationship, Table A can have more than one
matching record in Table B AND tblB can have more than one matching
record in tblA. This type of relationship cannot be specified to Access
as such; you have to break up a m:n relation into TWO 1:m relationships
by using a junction or "resolver" table. Put the pk's from each of the
two tables into the resolver table, which then acts as sort of a traffic
cop between the other two tables. Again, refer to the Northwinds db for
an example...the OrderDetails table resolves a m:n relationship between
the Orders and Products tables (each Order can include one OR MORE
Products, AND each Product can be ordered one OR MORE times).

- One-to-one (1:1) relationships are rare, and generally (but not
always) mean you need to rethink your table design. In this relationship
type, tblA can have only one matching record in tblB, and tblB can have
no more than one matching record in tblA. If entities are related 1:1,
that usually (but not always) means their attributes can be combined in
a single table.

This MS support site contains links to many excellent resources on
relational database design:

http://support.microsoft.com/default...b;EN-US;289533

Now, using Allen’s donor db as an example:

Take a look at the GroupTypes table. As the text explains, there can be
more than one type of group making contributions. Here’s some example
data to help you get your head around this idea:

GrpTypeID GrpTypeName
1 Corporate
2 Nonprofit
3 Individual

The TypeID is the pk for this table.

Why not just put this information in tblGroups, you may ask? The answer
is, this way the name of each group type only has to be typed in ONCE.
To clarify this, here’s some dummy data from tblGroups:

GrpID GrpName TypeID
1 IBM Corporate
2 Microsoft Corprate
3 Gen Motors Corpororate
4 PBS Nonprofit
5 NEH Non-profit
6 Joe Blow Individual
7 Jane Doe Indiv
8 Richard Roe Ind

Compare this to:

GrpID GrpName TypeID
1 IBM 1
2 Microsoft 1
3 Gen Motors 1
4 PBS 2
5 NEH 2
6 Joe Blow 3
7 Jane Doe 3
8 Richard Roe 3

You see where I’m going here? Users need never even see Type ID or
GrpID. These key fields exist to uniquely identify each record in their
respective tables AND to help Access keep track of related information
in tables. But users “look up” the correct group type & select it from
the data entry form (based on a query that includes both tblGroupTypes
and tblGroups)…they “see” the Type Name, but Access stores the TypeID,
and the user is never the wiser. This helps head off errors when
entering data, and typos when attempting to retrieve data via queries (a
query for all groups WHERE GrpTypeName=”Corporate” will return only 1
record from tblGroups; WHERE GrpTypeName=”Croprate” will return no
records at all). Incidentally, the above example presumes a 1:m
relationship between GrpTypes and Groups...group types may appear one OR
MORE times in tblGroups, but each group represents ONE AND ONLY ONE
Group Type. The TypeID field is an autonumber in tblGroupTypes, but a
Number (Long) in tblGroups; you can’t join autonumber to autonumber

Apologies for length, but I hope this helps you get handle on pk’s. If
not, post back and I’ll try to explain it better.

Good luck,

LeAnne


Khai wrote:
table1
eID as long (autonumber)
miscinfo as text
moreinfo as text

table2
eID as long?
e2ID as long?
yetmoreinfo as text

Say I had multiple table2's for each table1. Would eID in table2 be set to
the same value in table1.eID, and then, would that mean e2ID would be a
unique key on it's own, labeled PK? Is this where Foreign Keys are? So
table1.eID and table2.eID equal each other?

I understand the reasoning for PK's - but having trouble understanding the
concept and how exactly they relate to another table. =/

in the example relationship dbase at
http://members.iinet.net.au/~allenbrowne/AppHuman.html (I found that in
another post here in the group), it has GroupTypeID listed in tblGroup and
tblGroupType. Say they're long autonumbers, do you defined tblGroupID as
Long (Autonumber), or just as Long, and on Creation, set it to
tblGroupType.GroupTypeID ? Then, if that were the case, how would you pull
up all tblGroups in a form that were listed to tblGroupType.GroupTypeID?

*wandering around in circles*
DPHarr, hopeful database creator.


  #3  
Old January 20th, 2005, 09:47 PM
Khai
external usenet poster
 
Posts: n/a
Default

LeAnne, I've seen your stuff, and know you're heavily involved in the
newsgroups, and I just have to say - I love you! hehe.

Having read all that below, and re-reading my original post, I have to ask
one minor question.
Each client can have multiple applications (so 1:m, right?) if eID% is a
random autoincrement, when I add an application, do I have to make the
ClientID = tblClient.eID or... ? do I have to type it in, or can I
programattically assign it? So I can look up a client, then see all
applications for that client? But then, on the other side, I can pull up an
Application, and go the other way to see which client it belong to? (This
is a much more simplified version of what I'm trying to do. hehe.)

tblClient
First$
Last$
eID% (PK)
myApplication$

tblApplications
clientID
Date
Amount ($000.00)
AppID% (PK)

-dpharr

"LeAnne" wrote in message
...
Whoa there, young feller. Take a deep breath. Now take another. I think
you need to step out of the circle and consider some basic tenets of
relational databases.

First: Access creates Relationships when you join tables in the
Relationships window. It's up to you to develop & set up your db schema
in such a way that relationships between tables are clearly understood
by Access, and are therefore clearly defined (not of the dreaded
"indeterminate" type) when created. Access then uses the relationships
to find associated information in your db quickly and efficiently.

As with anything, you have to start at the beginning. What you
essentially have to do is chop your data down to their most atomic and
logical components (aka "decomposing"), then figure out how to put them
all back together again in meaningful ways. Ask yourself, what are the
entities (real-life persons, places, things, or events that the db will
keep track of) and their attributes (categories of related information
relevent to each entity)? Entities & attributes = tables & fields. Once
you have determined what tables and fields the db needs, then you have
to set your primary keys (using one or more fields) to uniquely identify
each record in a table.

Then once the pk's are in place, you have to tell Access how to bring
all the information together again using criteria that you will set when
you query the db (e.g. "All orders for fiscal year 1999", "All streams
in the Shenandoah River watershed", "All contributions by corporate
donors greater than $10,000," etc.). To set up relationships between
tables, add one table's pk to the other table (in which case it's known
as a foreign key). To decide which table's pk should go where, you have
to figure out how entities are related to each other:

- In a one-to-many relationship (1:m), a record in Table A can have more
than one matching record in Table B, but a record in Table B has no more
than one matching record in table A. To set up this relationship, add
the field(s) that make up the pk on the "one" side of the relationship
to the table on the "many" side. Check out the Northwinds example
database included with most versions of Access for an example of such a
relationship between the Suppliers and Products tables; one supplier can
supply more than one product, but each product has only one supplier.

- In a many-to-many (m:n) relationship, Table A can have more than one
matching record in Table B AND tblB can have more than one matching
record in tblA. This type of relationship cannot be specified to Access
as such; you have to break up a m:n relation into TWO 1:m relationships
by using a junction or "resolver" table. Put the pk's from each of the
two tables into the resolver table, which then acts as sort of a traffic
cop between the other two tables. Again, refer to the Northwinds db for
an example...the OrderDetails table resolves a m:n relationship between
the Orders and Products tables (each Order can include one OR MORE
Products, AND each Product can be ordered one OR MORE times).

- One-to-one (1:1) relationships are rare, and generally (but not
always) mean you need to rethink your table design. In this relationship
type, tblA can have only one matching record in tblB, and tblB can have
no more than one matching record in tblA. If entities are related 1:1,
that usually (but not always) means their attributes can be combined in
a single table.

This MS support site contains links to many excellent resources on
relational database design:

http://support.microsoft.com/default...b;EN-US;289533

Now, using Allen’s donor db as an example:

Take a look at the GroupTypes table. As the text explains, there can be
more than one type of group making contributions. Here’s some example
data to help you get your head around this idea:

GrpTypeID GrpTypeName
1 Corporate
2 Nonprofit
3 Individual

The TypeID is the pk for this table.

Why not just put this information in tblGroups, you may ask? The answer
is, this way the name of each group type only has to be typed in ONCE.
To clarify this, here’s some dummy data from tblGroups:

GrpID GrpName TypeID
1 IBM Corporate
2 Microsoft Corprate
3 Gen Motors Corpororate
4 PBS Nonprofit
5 NEH Non-profit
6 Joe Blow Individual
7 Jane Doe Indiv
8 Richard Roe Ind

Compare this to:

GrpID GrpName TypeID
1 IBM 1
2 Microsoft 1
3 Gen Motors 1
4 PBS 2
5 NEH 2
6 Joe Blow 3
7 Jane Doe 3
8 Richard Roe 3

You see where I’m going here? Users need never even see Type ID or
GrpID. These key fields exist to uniquely identify each record in their
respective tables AND to help Access keep track of related information
in tables. But users “look up” the correct group type & select it from
the data entry form (based on a query that includes both tblGroupTypes
and tblGroups)…they “see” the Type Name, but Access stores the TypeID,
and the user is never the wiser. This helps head off errors when
entering data, and typos when attempting to retrieve data via queries (a
query for all groups WHERE GrpTypeName=”Corporate” will return only 1
record from tblGroups; WHERE GrpTypeName=”Croprate” will return no
records at all). Incidentally, the above example presumes a 1:m
relationship between GrpTypes and Groups...group types may appear one OR
MORE times in tblGroups, but each group represents ONE AND ONLY ONE
Group Type. The TypeID field is an autonumber in tblGroupTypes, but a
Number (Long) in tblGroups; you can’t join autonumber to autonumber

Apologies for length, but I hope this helps you get handle on pk’s. If
not, post back and I’ll try to explain it better.

Good luck,

LeAnne


Khai wrote:
table1
eID as long (autonumber)
miscinfo as text
moreinfo as text

table2
eID as long?
e2ID as long?
yetmoreinfo as text

Say I had multiple table2's for each table1. Would eID in table2 be set

to
the same value in table1.eID, and then, would that mean e2ID would be a
unique key on it's own, labeled PK? Is this where Foreign Keys are? So
table1.eID and table2.eID equal each other?

I understand the reasoning for PK's - but having trouble understanding

the
concept and how exactly they relate to another table. =/

in the example relationship dbase at
http://members.iinet.net.au/~allenbrowne/AppHuman.html (I found that in
another post here in the group), it has GroupTypeID listed in tblGroup

and
tblGroupType. Say they're long autonumbers, do you defined tblGroupID

as
Long (Autonumber), or just as Long, and on Creation, set it to
tblGroupType.GroupTypeID ? Then, if that were the case, how would you

pull
up all tblGroups in a form that were listed to tblGroupType.GroupTypeID?

*wandering around in circles*
DPHarr, hopeful database creator.




  #4  
Old January 20th, 2005, 10:47 PM
LeAnne
external usenet poster
 
Posts: n/a
Default

Hi Khai,

Khai wrote:

LeAnne, I've seen your stuff, and know you're heavily involved in the
newsgroups, and I just have to say - I love you! hehe.


Gee, thanks...blushing

Having read all that below, and re-reading my original post, I have to ask
one minor question.
Each client can have multiple applications (so 1:m, right?) if eID% is a
random autoincrement, when I add an application, do I have to make the
ClientID = tblClient.eID or... ? do I have to type it in, or can I
programattically assign it? So I can look up a client, then see all
applications for that client? But then, on the other side, I can pull up an
Application, and go the other way to see which client it belong to? (This
is a much more simplified version of what I'm trying to do. hehe.)

tblClient
First$
Last$
eID% (PK)
myApplication$

tblApplications
clientID
Date
Amount ($000.00)
AppID% (PK)

-dpharr

Ok, if each client can apply multiple times, but each specific
application refers to one and only one client, then yep, it's a 1:m
relationship. And yep, you need some linking field (or combination of
fields) to clarify that relationship to Access. If eID is the same thing
as ClientID (if so, this seems confusing to me...I prefer keeping the
fieldnames the same for clarity), and eID is an incrementing autonumber
in tblClients, then ClientID would be the linking field, the foreign key
in tblApplications, with field datatype Number (Long). tbltblClients
would be joined one-to-many to tblApps on eID=ClientID. You don't have
to do this "every time you add an application;" the relationship is
(hopefully) already defined. Then by including both of these tables in
a query, you can ask Access to "Show me a list of client names and all
of their associated applications" like so:

SELECT Lastname, FirstName, AppID, other fields as needed
FROM tblClients INNER JOIN tblApplications ON
tblClients.eID=tblApplications.ClientID;

or, "Show me the name of the client associated with Application 42" like
this:

SELECT AppID, FirstName, LastName
FROM tblClients INNER JOIN tblApplications ON
tblClients.eID=tblApplications.ClientID
WHERE tblApplications.AppID=42;

Note that if you have already specified the relationships correctly to
Access, an INNER JOIN works fine.

Having said all that, I'm unclear as to why you have the myApplications
field in tblClients. Remember, tables are groups of RELATED attributes.
tblClients should contain ONLY info pertinent to Clients (e.g.
FirstName, LastName {I'd avoid using "First" and "Last" as fieldnames as
they are reserved words), Street Address, City, State, Zip, OfficePhone,
CellPhone, FaxNum, etc.).

One thing more...you ARE using a form to input your data, right??? Data
entry via tables is generally considered A Bad Idea. If you don't have a
data entry form to act as the user interface to your tables, then you
will have to first enter the correct data in tblClients, let Access
assign the eID using the counter, and then remember AND manually type
that number into tblApplications each time you add another record to
tblApplications. I recommend you search Help for the topics "Forms: What
they are and how they work" and "Ways to work with data in a form" to
find out more about creating a data entry form which you can use to
enter data into multiple tables.

hth,

LeAnne





"LeAnne" wrote in message
...

Whoa there, young feller. Take a deep breath. Now take another. I think
you need to step out of the circle and consider some basic tenets of
relational databases.

First: Access creates Relationships when you join tables in the
Relationships window. It's up to you to develop & set up your db schema
in such a way that relationships between tables are clearly understood
by Access, and are therefore clearly defined (not of the dreaded
"indeterminate" type) when created. Access then uses the relationships
to find associated information in your db quickly and efficiently.

As with anything, you have to start at the beginning. What you
essentially have to do is chop your data down to their most atomic and
logical components (aka "decomposing"), then figure out how to put them
all back together again in meaningful ways. Ask yourself, what are the
entities (real-life persons, places, things, or events that the db will
keep track of) and their attributes (categories of related information
relevent to each entity)? Entities & attributes = tables & fields. Once
you have determined what tables and fields the db needs, then you have
to set your primary keys (using one or more fields) to uniquely identify
each record in a table.

Then once the pk's are in place, you have to tell Access how to bring
all the information together again using criteria that you will set when
you query the db (e.g. "All orders for fiscal year 1999", "All streams
in the Shenandoah River watershed", "All contributions by corporate
donors greater than $10,000," etc.). To set up relationships between
tables, add one table's pk to the other table (in which case it's known
as a foreign key). To decide which table's pk should go where, you have
to figure out how entities are related to each other:

- In a one-to-many relationship (1:m), a record in Table A can have more
than one matching record in Table B, but a record in Table B has no more
than one matching record in table A. To set up this relationship, add
the field(s) that make up the pk on the "one" side of the relationship
to the table on the "many" side. Check out the Northwinds example
database included with most versions of Access for an example of such a
relationship between the Suppliers and Products tables; one supplier can
supply more than one product, but each product has only one supplier.

- In a many-to-many (m:n) relationship, Table A can have more than one
matching record in Table B AND tblB can have more than one matching
record in tblA. This type of relationship cannot be specified to Access
as such; you have to break up a m:n relation into TWO 1:m relationships
by using a junction or "resolver" table. Put the pk's from each of the
two tables into the resolver table, which then acts as sort of a traffic
cop between the other two tables. Again, refer to the Northwinds db for
an example...the OrderDetails table resolves a m:n relationship between
the Orders and Products tables (each Order can include one OR MORE
Products, AND each Product can be ordered one OR MORE times).

- One-to-one (1:1) relationships are rare, and generally (but not
always) mean you need to rethink your table design. In this relationship
type, tblA can have only one matching record in tblB, and tblB can have
no more than one matching record in tblA. If entities are related 1:1,
that usually (but not always) means their attributes can be combined in
a single table.

This MS support site contains links to many excellent resources on
relational database design:

http://support.microsoft.com/default...b;EN-US;289533

Now, using Allen’s donor db as an example:

Take a look at the GroupTypes table. As the text explains, there can be
more than one type of group making contributions. Here’s some example
data to help you get your head around this idea:

GrpTypeID GrpTypeName
1 Corporate
2 Nonprofit
3 Individual

The TypeID is the pk for this table.

Why not just put this information in tblGroups, you may ask? The answer
is, this way the name of each group type only has to be typed in ONCE.
To clarify this, here’s some dummy data from tblGroups:

GrpID GrpName TypeID
1 IBM Corporate
2 Microsoft Corprate
3 Gen Motors Corpororate
4 PBS Nonprofit
5 NEH Non-profit
6 Joe Blow Individual
7 Jane Doe Indiv
8 Richard Roe Ind

Compare this to:

GrpID GrpName TypeID
1 IBM 1
2 Microsoft 1
3 Gen Motors 1
4 PBS 2
5 NEH 2
6 Joe Blow 3
7 Jane Doe 3
8 Richard Roe 3

You see where I’m going here? Users need never even see Type ID or
GrpID. These key fields exist to uniquely identify each record in their
respective tables AND to help Access keep track of related information
in tables. But users “look up” the correct group type & select it from
the data entry form (based on a query that includes both tblGroupTypes
and tblGroups)…they “see” the Type Name, but Access stores the TypeID,
and the user is never the wiser. This helps head off errors when
entering data, and typos when attempting to retrieve data via queries (a
query for all groups WHERE GrpTypeName=”Corporate” will return only 1
record from tblGroups; WHERE GrpTypeName=”Croprate” will return no
records at all). Incidentally, the above example presumes a 1:m
relationship between GrpTypes and Groups...group types may appear one OR
MORE times in tblGroups, but each group represents ONE AND ONLY ONE
Group Type. The TypeID field is an autonumber in tblGroupTypes, but a
Number (Long) in tblGroups; you can’t join autonumber to autonumber

Apologies for length, but I hope this helps you get handle on pk’s. If
not, post back and I’ll try to explain it better.

Good luck,

LeAnne


Khai wrote:

table1
eID as long (autonumber)
miscinfo as text
moreinfo as text

table2
eID as long?
e2ID as long?
yetmoreinfo as text

Say I had multiple table2's for each table1. Would eID in table2 be set


to

the same value in table1.eID, and then, would that mean e2ID would be a
unique key on it's own, labeled PK? Is this where Foreign Keys are? So
table1.eID and table2.eID equal each other?

I understand the reasoning for PK's - but having trouble understanding


the

concept and how exactly they relate to another table. =/

in the example relationship dbase at
http://members.iinet.net.au/~allenbrowne/AppHuman.html (I found that in
another post here in the group), it has GroupTypeID listed in tblGroup


and

tblGroupType. Say they're long autonumbers, do you defined tblGroupID


as

Long (Autonumber), or just as Long, and on Creation, set it to
tblGroupType.GroupTypeID ? Then, if that were the case, how would you


pull

up all tblGroups in a form that were listed to tblGroupType.GroupTypeID?

*wandering around in circles*
DPHarr, hopeful database creator.





 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Are Primary keys needed in child tables? Nevie and Phil Database Design 2 January 16th, 2005 08:23 AM
Logical question on primary keys... Access rookie Database Design 4 January 8th, 2005 12:26 PM
Using Primary Keys Jodie General Discussion 1 July 14th, 2004 08:49 PM
Choosing Primary and Foreign Keys A.V.H New Users 8 May 23rd, 2004 09:12 PM


All times are GMT +1. The time now is 11:59 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.