Table relationships

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello All,

I can't for the life of me understand table relationships and how they work.
I've tried reading up on it on Microsofts website but it sounds really
confusing. Any other sites where it might explain it better? Is the reason I
don't understand because my database has five tables in it and none of the
fields are the same or relate to any other table? Should I make another table
and combine all the fields from the other five so that I can relate them?


TIA,
David
 
Your name is David and you have a last name. You have a social security
number. You have an address, telephone number, perhaps a cell phone number,
and other demographic information. All this would be in one table named
perhaps TblPeople. In everyday life you need to be identified uniquely from
all other people. The world uses your social security number for that. So
SSN is the primary key of TblPeople because it is the only piece of data
that is unique to all records in the table. In other words, tell me your
social security number and I can pull up your record and tell you all the
information for you stored in TblPeople.

The IRS has TblPeople and a TblTaxes. There's a SSN field in TblTaxes as
well as many other tax related fields. The tax related fields as a whole
tell the IRS for one thing your income from all sources. You may have
multiple sources of income so there is a one-to-many relationship between
you and your sources of income. This is what makes TblTaxes necessary. So
tell me your SSN and I can tell you all your sources of income from
TblTaxes. The IRS needs to send you your Federal Income Tax return booklet
about this time of the year. If they only had TblTaxes they would not know
your name or address; TblTaxes only has your SSN. The IRS creates a
relationship between TblTaxes and TblPeople based on SSN so now they can
associate Tax information to a real person. From TblPeople they have your
name and address and are able to send you your tax booklet.

SSN in TblPeople is called the primary key in the relationship and SSN in
TblTaxes is called the foreign key in the relationship. Your tables would
look like:
TblPeople
SSN
<all other demographic fields>

TblTaxes
SSN
SourceOfIncome
Income
<all other tax related fields>
 
David said:
Hello All,

I can't for the life of me understand table relationships and how they work.
I've tried reading up on it on Microsofts website but it sounds really
confusing. Any other sites where it might explain it better? Is the reason I
don't understand because my database has five tables in it and none of the
fields are the same or relate to any other table? Should I make another table
and combine all the fields from the other five so that I can relate them?


TIA,
David
Click to expand...

<major db neepery>

IMO, there is no way getting around understanding the basics.

The trouble could come from the fact that Relation, Relations, and
Relationship(s) have multiple meanings in regards to "Relational
Databases."

One meaning, is that the underlying theory behind Relational Databases is a
branch of math called Relational Algebra (invented by Dr. Codd). This
branch of math works on "sets" of data. A "set" in Relational Algebra is
called a "relation" (don't ask me why . . .). Then, some people in various
companies (IBM, Oracle, etc.) took this branch of math and made it into the
precursors of the Relational Databases apps we have today (they grabbed
"relational" off of Relational Algebra and stuck it in front of "database",
and voila, we have "Relational Databases").

Now, in order to run our databases, we have something that goes by the
technical term, Referential Integrity. This states that if we start with
Table CustomerMaster, that has a Primary Key of SocialSecurityNumber, and we
have Table CustomerPhone, that also has a column called SocialSecurityNumber
(so that we know who the phone numbers belong to); whatever happens, we
don't want to have a row in the CustomerPhone table that has a Social
Security Number that doesn't appear in CustomerMaster. Why? Because we'd
have phone numbers in CustomerPhone that belonged to no one we could
identify (there being nothing in CustomerMaster). Relational Databases use
internal management, and indexes, to *enforce* a "rule" that automatically
prevents users from deleting any row in CustomerMaster . . . *when there is
still any row* . . . in CustomerPhone with the Social Security Number about
to be deleted from CustomerMaster. This process, this "enforcement" of the
"rules", is called Referential Integrity.

Now, in MS Access, you open up the "Relationships" window, and drag fields
back and forth and establish nice little lines that go from table to table,
and MS Access calls these lines "Relationships" (and a lot of other people
do, too). In this case, an MS Access "Relationship" is the method whereby
"Referential Integrity" is set up.

To further confuse the issue (yes, there's more), Data Modeling, the process
where ideas about things are organized into understandable formats, like big
charts covered by boxes with lines running between them (rather remarkably
like the boxes and lines in the MS Access Relationships window), also call
their lines "relationships".

Is there more? Yes. Relationships in Data Modeling and Relationships in
generic Database Design (the process of planning out and building a
database; which, ideally, proceeds from a completed Data Model) are similar,
but not the same.

In Database Design, relationships frequently refer to the establishment of
Referential Integrity (RI).


Now, jumping a little out of the relationship picture, we go further into
Database Design. There are three main rules of good table design. They are
Called First Normal Form, Second Normal Form, and Third Normal Form (often
abbreviated 1NF, 2NF, and 3NF). There's also BCNF and 4NF. These rules,
when followed, among other things, make establishing RI easier (they make
the whole DB Design process easier, most of the time). Look them these
rules on the internet (you may already have, I am sliding away from the
original topic).

</major db neepery>
 
David

The problem with using a nationally-sponsored ID is that it really only
applies to folks registered with that nation. While a SSN (social security
number) can be obtained from the USA, not every country in the world would
likely have access to that database, nor would THEIR citizens likely be
registered there.

More over, there are plenty of examples of "identity theft", involving more
than one person using the same SSN.

For these two reasons (and perhaps more), this is a great example of the
difficulty of identifying and using a true unique identifier.

Good luck

Jeff Boyce
<Access MVP>
 
Back
Top