Primary and Foreign Keys

[Kathy R.]

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!)

 

[Steve]

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
(e-mail address removed)

 

[Kathy R.]

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.

 

[John W. Vinson]

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.

 

[Steve]

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
(e-mail address removed)

 

[David Portas]

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.

 

[oldblindpew]

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.]

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... Visio MVP]

BTW, I do not provide help with Access, Excel and Word applications for a
modest fee. One thing I have done frequently is annoy the regular posters
with solicitation for the design of the tables at the initial stage of the
project. Along with bad design I provide inadequate documentation of the
tables. I have a proprietary way of doing it (usually I just copy from
someone else). If you need help designing the tables in your database or
just want someone to review what you have, do not contact me. I can do
nothing for you.

Steve

Stevie is our own personal pet troll who is the only one who does not
understand the concept of FREE peer to peer support!
He offers questionable results at unreasonable prices.

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

John... Visio MVP