Home‎ > ‎6 pm class pages‎ > ‎

Daniel Pierce

Table relationships; primary key, foreign key, one-to-many, one-to-one, many-to-many


Table Relationships, Wikipedia page

Table Relationships


Primary Key:
    
    The primary key of a relational table identifies the records inside the table. It’s usually something like a Social Security Number (which is guaranteed to be different from the other records) or can be created by something called the DBMS.  Primary keys can have single or multiple attributes.  For example, if San Jose State had a STUDENTS table in their database, a good primary key would be a student’s ID number, because not one ID number would be alike.  A bad primary key choice would be a student’s first or last name, because there is likely to be duplicates.    
    
    A primary key is known to be one of the most critical parts of designing a new database.  You need to make sure the primary key is unique.  If two records contain the same value, it is not a good choice for a primary key.  This also includes the entire history of the database, so make sure past records also have unique values.  It will help if you get creative with this key, but all you have to do is make sure no keys have an exact match.

Foreign Key:
    
    A foreign key can simply be defined as a referential constraint between two tables.  A foreign key is a field and its main job is to match candidate keys of different tables.  It can also be used to cross-reference tables.  For example, say we have a company that has a database containing a CUSTOMER table and an ORDERS table, and wants to match the orders with the correct customers.  To successfully do this, you would place a foreign key into the ORDERS table, and match it up with the primary key in the CUSTOMER table.

    A foreign key can also be looked at as an identifier of a column or set of columns in one table that comes from another table.  The original data is part of the referenced table, and the referencing table is the proceeding data.  The columns in the referencing table are usually the ones with the primary key.  All the values that are in the referencing columns should be similar to a single row in the referenced table.  In other words, the values in the referencing table must match up with the values in the referenced table.

Foreign Key, Wikipedia page

One-to-Many:
    
    One-to-many is quite self-explanatory; when one record in one table relates to many records in another table.  An example of this would be if you had a table of sports teams, and then had the players playing on each of those teams in another table.  You have to be very careful when dealing with one-to-many relationships.  Make sure when you join the tables, that you do it in the correct order.  For the above example, you must join the sports teams table to the players table, and you’ll end up with a table listing the sports team associated with each player.  If you do this the opposite way, joining the player’s table with the sports team, you’ll end up with only one player on each team.

One-to-One:

    One-to-one relationships are simply when one record in a table relates to only one record in another table.  Perhaps you have a table with source A, B, C, etc., a one-to-one relationship would have another table with the location of A, B, C, etc.  You can word it as additional information on one record that can be found in only one record in another table.  Tables with one-to-one relationships can be joined without any problems, as long as you’re joining common fields.

Many-to-Many:

    Many-to-many relationships are when many records in one table will relate to many records in another.  Imagine two entities A and B, in which A is seen as a parent row for which B contains many children.  Say A is Coaches, and B is Players.  A player can play for many coaches, and a coach can coach many players.  Most databases only feature one-to-many relationships, so it’s necessary to create many-to-many relationships manually through a third junction table.  The way you can do this is by putting two one-to-many relationships in an AB table (A -> AB, and B -> AB).  The primary key will be formed for AB from the two foreign keys.

Many-to-Many, Wikipedia page

Additional Information: Linking a Database to Reach Indexing

Videos:

Primary and Foreign Keys


One-to-Many


One-to-One

Many-to-Many


Comments