Some Discussion of HPM
PP106 - 117
Keys and Relationships

 

Keys and Foreign Keys.  A key in a table is a unique identifier associated with each record.  A table can have more than one key, and one of these needs to identified as the primary key.  Some examples of keys are

  • the autonumber field incremented for each new entry
  • a social security number (though not everyone has one)
  • drivers license number for those that have them
  • ISBN number for books

When a key from one table is used in another table to link back to information in the table where it is a key, the key that appears in the other table is called a foreign key.  It is foreign to the table it is in, but it is a unique identifier or link back to data in the other table.

This can save repeating a lot of information.

One-to-Many Relationships.  These sorts of relationships between tables exist when the key of a record in one table, often called the parent table, can appear many times in the child table where it is used as a foreign key.

  • For example, you can have a customer table with a primary key id_customer.  But rather than re-entering all the customer information into the orders table when a customer places an order you only need to enter the foreign key fk_id_customer that refers back to the information in the customer table.  The notation fk_id_customer is my own approach to making it easier to identify foreign keys.

 

 

The diagram represents how information about the customer that placed a particular order can be obtained from using the foreign key for the customer fk_id_customer and the record it points to in the Customer table.  Because each customer can place many orders, the relationship is said to be one-to-many.
  • You might have a table of all the fathers in a church with id_father as the primary key.  Then you have a child table with id_child as the primary key.  The information about the father of the child isn't rewritten in the child table.  It is found by using the foreign key fk_id_father in the child table.

 

 

Father
 
id_father name_father birth_city
1 Frank Drebbin Cleveland, OH
2 Sam Shepard Detroit, MI
3 Tom Treble Toledo, OH
4 Tad Everett Erie, PA
 
Child
 
id_child fk_id_father name_child
1 2 Sarah
2 3 Charlotte
3 2 Sander
4 2 Carl
5 4 Karla

 

So you can find out the name and birth city, or any other information in the table, about the father of the child by using the foreign key, fk_id_father as the key in the Father table.

This sort of relationship is also called a parent table with the child table when used in general.

One-to-One Relationships.  When the key used in the parent table will appear exactly once in the child table you are said to have a one-to-one relationship.  This relationship doesn't always have to exist.  But if it does then there can be at most one connection for each record.

One of the most obvious of these might arise in an employee and spouse table.  This could be useful for medical benefits or a number of other reasons.  Rather than draw a relationship connection I am going to illustrate this with two tables.

 

Employee
 
id_employee name_last name_first address
17239 Drebbin Frank  
17240 Shepard Sam  
17241 Blake Kathy  
17242 Everett Sharon  
 
Spouse
 
id_spouse fk_id_employee name_first
1 17239 Laura
2 17241 Carl
3 17242 Sander
     
     

 

Many-to-One Relationships.  Many people call these table lookup relationships.  It probably will be interesting to see if you can come up with a Many-to-One Relationship that can't be considered a table lookup.

Consider the following situation.

 

Employee
 
id_employee name_last name_first address zip
17239 Drebbin Frank   44140
17240 Shepard Sam   44145
17241 Blake Kathy   44140
17242 Everett Sharon   44140
17243 Strauss Levi   44145
17244 Karan Donna   44011
17245 Frank Cesar   44145
 
Location
 
id_zip city state
44140 Bay Village Ohio
44145 Westlake Ohio
44011 Avon Ohio
     
     

 

It is pretty reasonable to think of other situations where this sort of setup can help save a lot of space and repeated information.  For example, you might have a list of employees of other firms that are currently working on your site in a consortium.  Rather than list the complete address for each employee's workplace, you have a single table that has all the background information on each company in the consortium.  Then you can have some sort of field in each person's record that has a single reference to their firm.

Hopefully, it is getting easier to think of other many-to-one relationships.

Many-to-Many Relationships.  These sorts of situations arise quite frequently.  Think of people and the organizations they belong to.  My development of this will make use of three tables to ensure that everything has meaning.

It is not uncommon to use this sort of middle association table to interconnect records.

 

Residents
 
id_resident last_name first_name
202 Drebbin Frank
203 Struthers Sally
204 Strauss Sam
205 Garner Jim
Memberships
 
fk_id_resident fk_id_organization
202 1002
203 1007
205 1005
202 1006
204 1004
203 1006
204 1003
Organizations
 
id_organization name
1001 Amnesty International
1002 World Wildlife Fund
1003 Nature Conservancy
1004 Audubon Society
1005 Republican Party
1006 Democratic Party
1007 Heifer.org

 

So the many-to-many relationship that I want to focus on is between Residents and Memberships.  Notice how the records aren't necessarily in order.

These sorts of association tables such as this Memberships table really do arise in a number of ways.  They can be a bit frustrating to work with.

In even standard e-commerce web databases I have seen them used to associate products with departments.  I have also seen them used to associate products with their attributes such as available colors and sizes.

Cardinality.  So we have four basic types of relationship pairings.

  • one-to-many
  • one-to-one
  • many-to-one
    • also called table lookups
  • many-to-many

Over time I will develop more examples.

But now we need to define a few more concepts.  The minimum cardinality of a relationship is the minimum number of instances of entity B that must be associated with each instance of entity A.  Most of the time this will be 0.  But, for example, if your A entity is parents and your B entity is children, then the minimum cardinality must be one, otherwise they couldn't be parents.

The maximum cardinality of a relationship is the maximum number of entity instances B that may be associated with each instance of A.  Most of the time this will be unlimited.  For example, each customer can place as many orders as they want, or each set of parents can have as many children as they want.  But maybe you live in a country that is limiting the number of children each set of parents can have.  Or maybe you can only use the ATM up to three times each month.

Modeling Time Dependent Data.  When teaching at Quinnipiac we had quite a few students that worked for Sikorsky.  They were usually working on some sort of government projects that required a lot of recording of the history of the changes and developments on their projects.  They would usually use time stamps associated with particular data to make sure they could reconstruct what has happened over time.

One might also do something like change which products are contained in a product line and/or change their prices over time.  In order to compute things like annual sales figures for product lines you need to be able to reconstruct prices and which products were in the line at different times.