Primary and Foreign Keys

It's the end of the day and I'm second-guessing myself and need some
clarification regarding Primary and Secondary Keys and which tables they
should be in.

In a one-to-many relationship, the primary key is on the "one" side and
the foreign key is on the "many" side, right? I.e. an Individual can
have many phone numbers.

tblIndividual
IndID (PK)
FirstName
LastName

tblPhone
PhoneID (PK)
PhIndID (FK)
phonenumber
phonetype

What about in a one-to-one relationship? Does it really matter which
the foreign key goes in? Is there an acceptable/preferred practice?

I have a membership table where one of the fields is the manner in which
the individual joined. Since there's a limited number of ways to join,
but they can and do change over the years, I created a lookup table for
that bit of information. Currently (early stages of development) I have
the foreign key in the membership table. Since the membership table is
what I would consider the "main" table, should the primary key go into
the "child" table?

tblMembership
MembID (PK)
JoinDate
JoinOfficiant
MJoinMannerID (FK)

lkpJoinManner
JoinMannerID (PK)
JoinMannerCode
JoinManner

Thanks for your help!

Kathy R. (whose brain is currently mush - time for a break!)

You get a gold star today! 100%. Your statements are correct and your
examples are correct. One way to think about PK/FK is by way of a
TblIndividual. Social Security Number could be the PK in TblIndividual
because supposedly SSN is unique for each person. Then in all tables of data
pertaining to information about the individual, SSN could be the FK to
relate a record in that table to a specific individual in TblIndividual.

Steve




"Kathy R." wrote in message
...
It's the end of the day and I'm second-guessing myself and need some
clarification regarding Primary and Secondary Keys and which tables they
should be in.

In a one-to-many relationship, the primary key is on the "one" side and
the foreign key is on the "many" side, right? I.e. an Individual can have
many phone numbers.

tblIndividual
IndID (PK)
FirstName
LastName

tblPhone
PhoneID (PK)
PhIndID (FK)
phonenumber
phonetype

What about in a one-to-one relationship? Does it really matter which the
foreign key goes in? Is there an acceptable/preferred practice?

I have a membership table where one of the fields is the manner in which
the individual joined. Since there's a limited number of ways to join,
but they can and do change over the years, I created a lookup table for
that bit of information. Currently (early stages of development) I have
the foreign key in the membership table. Since the membership table is
what I would consider the "main" table, should the primary key go into the
"child" table?

tblMembership
MembID (PK)
JoinDate
JoinOfficiant
MJoinMannerID (FK)

lkpJoinManner
JoinMannerID (PK)
JoinMannerCode
JoinManner

Thanks for your help!

Kathy R. (whose brain is currently mush - time for a break!)

Thanks Steve for the reply, but you didn't answer my questions about the
one-to-one relationships

What about in a one-to-one relationship? Does it really matter which
the foreign key goes in? Is there an acceptable/preferred practice? And
I would add, why is one way better than another (assuming I could do it
either way)?

Kathy R.



Steve wrote:
You get a gold star today! 100%. Your statements are correct and your
examples are correct. One way to think about PK/FK is by way of a
TblIndividual. Social Security Number could be the PK in TblIndividual
because supposedly SSN is unique for each person. Then in all tables of data
pertaining to information about the individual, SSN could be the FK to
relate a record in that table to a specific individual in TblIndividual.

Steve




"Kathy R." wrote in message
...
It's the end of the day and I'm second-guessing myself and need some
clarification regarding Primary and Secondary Keys and which tables they
should be in.

In a one-to-many relationship, the primary key is on the "one" side and
the foreign key is on the "many" side, right? I.e. an Individual can have
many phone numbers.

tblIndividual
IndID (PK)
FirstName
LastName

tblPhone
PhoneID (PK)
PhIndID (FK)
phonenumber
phonetype

What about in a one-to-one relationship? Does it really matter which the
foreign key goes in? Is there an acceptable/preferred practice?

I have a membership table where one of the fields is the manner in which
the individual joined. Since there's a limited number of ways to join,
but they can and do change over the years, I created a lookup table for
that bit of information. Currently (early stages of development) I have
the foreign key in the membership table. Since the membership table is
what I would consider the "main" table, should the primary key go into the
"child" table?

tblMembership
MembID (PK)
JoinDate
JoinOfficiant
MJoinMannerID (FK)

lkpJoinManner
JoinMannerID (PK)
JoinMannerCode
JoinManner

Thanks for your help!

Kathy R. (whose brain is currently mush - time for a break!)

On Thu, 18 Feb 2010 16:27:58 -0500, "Kathy R." wrote:

What about in a one-to-one relationship? Does it really matter which
the foreign key goes in? Is there an acceptable/preferred practice?

Yes, it matters. There is still a directionality: one table is the "parent"
and the other is the "child", even if you are enforcing a One Child Policy.

One to one relationships are quite rare; the most usual case for them is
"Subclassing", in which you have a number of fields which only apply to a
subset of the records. For instance, you might have a membership table in
which some members are Officers, and there are a lot of fields which are
relevant only for Officers; you could have a one to one relationship from
Members (on the MemberID primary key) as the parent to Officers (joining on
the MemberID, which serves as both the primary AND foreign key in this case).


I have a membership table where one of the fields is the manner in which
the individual joined. Since there's a limited number of ways to join,
but they can and do change over the years, I created a lookup table for
that bit of information. Currently (early stages of development) I have
the foreign key in the membership table. Since the membership table is
what I would consider the "main" table, should the primary key go into
the "child" table?

tblMembership
MembID (PK)
JoinDate
JoinOfficiant
MJoinMannerID (FK)

lkpJoinManner
JoinMannerID (PK)
JoinMannerCode
JoinManner

Exactly correct. This is a perfectly normal one to many "lookup table"
scenario.
--

John W. Vinson [MVP]

See John's response. If you still have questions, post back and I will help.

BTW, I provide help with Access, Excel and Word applications for a modest
fee. One thing I have done frequently is help with the design of the tables
at the initial stage of the project. Along with design I provide
documentation of the tables. I have a proprietary way of doing it. If you
need help designing the tables in your database or just want someone to
review what you have, I can do that for you. If you want my help, contact
me.

Steve


"Kathy R." wrote in message
...
Thanks Steve for the reply, but you didn't answer my questions about the
one-to-one relationships

What about in a one-to-one relationship? Does it really matter which the
foreign key goes in? Is there an acceptable/preferred practice? And I
would add, why is one way better than another (assuming I could do it
either way)?

Kathy R.



Steve wrote:
You get a gold star today! 100%. Your statements are correct and your
examples are correct. One way to think about PK/FK is by way of a
TblIndividual. Social Security Number could be the PK in TblIndividual
because supposedly SSN is unique for each person. Then in all tables of
data pertaining to information about the individual, SSN could be the FK
to relate a record in that table to a specific individual in
TblIndividual.

Steve




"Kathy R." wrote in message
...
It's the end of the day and I'm second-guessing myself and need some
clarification regarding Primary and Secondary Keys and which tables they
should be in.

In a one-to-many relationship, the primary key is on the "one" side and
the foreign key is on the "many" side, right? I.e. an Individual can
have many phone numbers.

tblIndividual
IndID (PK)
FirstName
LastName

tblPhone
PhoneID (PK)
PhIndID (FK)
phonenumber
phonetype

What about in a one-to-one relationship? Does it really matter which
the foreign key goes in? Is there an acceptable/preferred practice?

I have a membership table where one of the fields is the manner in which
the individual joined. Since there's a limited number of ways to join,
but they can and do change over the years, I created a lookup table for
that bit of information. Currently (early stages of development) I have
the foreign key in the membership table. Since the membership table is
what I would consider the "main" table, should the primary key go into
the "child" table?

tblMembership
MembID (PK)
JoinDate
JoinOfficiant
MJoinMannerID (FK)

lkpJoinManner
JoinMannerID (PK)
JoinMannerCode
JoinManner

Thanks for your help!

Kathy R. (whose brain is currently mush - time for a break!)

"Kathy R." wrote in message
...
Thanks Steve for the reply, but you didn't answer my questions about the
one-to-one relationships

What about in a one-to-one relationship? Does it really matter which the
foreign key goes in? Is there an acceptable/preferred practice? And I
would add, why is one way better than another (assuming I could do it
either way)?

Kathy R.


1-1 is just a special case of a 1-N relationship where N is greater than 0
and is mandatory. In most SQL DBMSs it is usually impossible to enforce such
constraints because SQL foreign keys are always optional on one side. So for
example you can require an Item to have a parent Order but requiring an
Order to contain at least one Item is much harder.

The compromise workaround is generally to either allow optionality or to
enforce the rule through procedural code, for example using a DEFERRABLE
constraint.This means the constraint is temporarily disabled and violated
during updates.

Mandatory 1-N relationships are quite common business requirements and are
common in conceptual data models so it is actually a serious deficiency that
the SQL model of data can't easily implement them.

--
David Portas

"Steve" schreef in bericht ...
See John's response. If you still have questions, post back and I will help.

BTW, I provide help with Access, Excel and Word applications for a modest fee. One thing I have done frequently is help with the
design of the tables at the initial stage of the project. Along with design I provide documentation of the tables. I have a
proprietary way of doing it. If you need help designing the tables in your database or just want someone to review what you have,
I can do that for you. If you want my help, contact me.


--
Get lost $teve. Go away... far away....

Again... Get lost $teve. Go away... far away....
No-one wants you here... no-one needs you here...

This newsgroup is meant for FREE help..
No-one wants you here... no-one needs you here...
OP look at http://home.tiscali.nl/arracom/whoissteve.html
(Website has been updated and has a new 'look'... we have passed 11.000 pageloads... it's a shame !!)

Arno R

They have a rhetorical question in my locale: "Are you completely numb?"
What part do you not understand about the concept of a volunteer newsgroup?
I have spoken out againt pre-emptive attacks when you have not yet advertised
in a thread, but I am among those keeping an eye on you, and will call you
out when warranted. This is such a situation. You are out of line again.
Your inability to understand the concept of a volunteer newsgroup speaks
poorly of your capacity to grasp the complexities and subtleties of Access.

Besides, did you really expect to ingratiate yourself with the patronizing
remark about the gold star, especially when you did not get around to (or
could not) answer the question?

Steve wrote:
See John's response. If you still have questions, post back and I will help.

BTW, I provide help with Access, Excel and Word applications for a modest
fee. One thing I have done frequently is help with the design of the tables
at the initial stage of the project. Along with design I provide
documentation of the tables. I have a proprietary way of doing it. If you
need help designing the tables in your database or just want someone to
review what you have, I can do that for you. If you want my help, contact
me.

Steve


Thanks Steve for the reply, but you didn't answer my questions about the
one-to-one relationships
[quoted text clipped - 61 lines]

Kathy R. (whose brain is currently mush - time for a break!)

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201002/1

Isn't your JoinManner table just for lookup purposes, and therefore has a
one-to-many relationship with the Membership table? I'm no expert, but for
lookup tables, I haven't bothered with autonumber keys, but just use a
natural key, some sort of reasonable code, e.g. "CA" for "California".

I've just recently been trying the one-to-one approach for additional fields
not applicable to all records in the main table. The directionality is
established in the Edit Relationships window (Database Tools, Relationships,
right click on the relationship line). The main table is on the left and the
related table or sub-table is on the right.

In my case, a delete from the sub-table was causing an unexpected delete of
the main record (see recent thread in Database Design named "Unexpected
Delete"). I eventually gave up, at least for now, and put the additional
fields in the main table.

Hope this helps,
OldBlindPew


"Kathy R." wrote:

It's the end of the day and I'm second-guessing myself and need some
clarification regarding Primary and Secondary Keys and which tables they
should be in.

In a one-to-many relationship, the primary key is on the "one" side and
the foreign key is on the "many" side, right? I.e. an Individual can
have many phone numbers.

tblIndividual
IndID (PK)
FirstName
LastName

tblPhone
PhoneID (PK)
PhIndID (FK)
phonenumber
phonetype

What about in a one-to-one relationship? Does it really matter which
the foreign key goes in? Is there an acceptable/preferred practice?

I have a membership table where one of the fields is the manner in which
the individual joined. Since there's a limited number of ways to join,
but they can and do change over the years, I created a lookup table for
that bit of information. Currently (early stages of development) I have
the foreign key in the membership table. Since the membership table is
what I would consider the "main" table, should the primary key go into
the "child" table?

tblMembership
MembID (PK)
JoinDate
JoinOfficiant
MJoinMannerID (FK)

lkpJoinManner
JoinMannerID (PK)
JoinMannerCode
JoinManner

Thanks for your help!

Kathy R. (whose brain is currently mush - time for a break!)
.

Thank you all for your replies. As I was driving home last night, that
I was thinking in only one direction - a member has only one type of
join manner. And then I had a "duh!!" moment, turned it around and
realized that the join manner has many members.

I appreciate the information on the 1-to-1 relationships. It seems like
the more I learn the more I realize I've got a long, long way to go.

And Steve, thanks for the offer, but, should I ever decide to pay for a
professional, it will most certainly be one that understands the concept
of FREE help on these forums.

Kathy R.

John W. Vinson wrote:
On Thu, 18 Feb 2010 16:27:58 -0500, "Kathy R." wrote:

What about in a one-to-one relationship? Does it really matter which
the foreign key goes in? Is there an acceptable/preferred practice?

Yes, it matters. There is still a directionality: one table is the "parent"
and the other is the "child", even if you are enforcing a One Child Policy.

One to one relationships are quite rare; the most usual case for them is
"Subclassing", in which you have a number of fields which only apply to a
subset of the records. For instance, you might have a membership table in
which some members are Officers, and there are a lot of fields which are
relevant only for Officers; you could have a one to one relationship from
Members (on the MemberID primary key) as the parent to Officers (joining on
the MemberID, which serves as both the primary AND foreign key in this case).

I have a membership table where one of the fields is the manner in which
the individual joined. Since there's a limited number of ways to join,
but they can and do change over the years, I created a lookup table for
that bit of information. Currently (early stages of development) I have
the foreign key in the membership table. Since the membership table is
what I would consider the "main" table, should the primary key go into
the "child" table?

tblMembership
MembID (PK)
JoinDate
JoinOfficiant
MJoinMannerID (FK)

lkpJoinManner
JoinMannerID (PK)
JoinMannerCode
JoinManner

Exactly correct. This is a perfectly normal one to many "lookup table"
scenario.