Microsoft Access Tips Relationships – by Lisa Friedrichsen

19
55




About this tutorial:

Video duration: 5:20
Microsoft Access Tips by Lisa Friedrichsen
Table Relationships, One-to-Many relationships with referential integrity.
All of my youtubes are listed at: http://students.jccc.edu/lisalfri

19 COMMENTS

  1. But in general, if you see duplicated information in any other field of a table, you probably have an opportunity to improve the database by making a one-to-many relationship. The table with the duplicate values is always on the "many" or child side of the relationship. Pretty hard to clarify this well without a concrete example though.

  2. Hi Lisa, great video.. Now what if the unique ID or the primary key has duplicates in one table and duplicates in multiple columns not all of the fields, what do you recommend to do?

  3. You're describing a classic many-to-many relationship.  One inmate can be related to many families.  But one family can be related to many inmates too.  The solution to this is a junction table that sits between the Inmates and Families tables and is on the "many" side of a one-to-many relationship back to your two original tables (Inmates and Families).  

    You might call it the Connections table (for lack of a better name).  In the Connections table you place the TDCJ number as a foreign key field to the Inmates table.  So one inmate now has many Connections.  You also place the FamilyID field as a foreign key field in the Conections table (or whatever field you've designated as the primary key field in the Families table).  So one Family can also have many connections which solves your original problem.   Now one inmate can be related to many families and one family can be related to many inmates.  Post again if you have questions on this.  

    PS:  The Order Items table is a sample in Northwind.  It is the junction table between Orders and Products given Orders and Products have a many-to-many relationship.

  4. @Ryan&@Jonathan– in the Order Details table the OrderID and ProductID fields individually are foreign key fields that help establish one-to-many relationships.  The key symbol by BOTH means that the values in the two fields taken together must be unique, a composite primary key situation.  Individually, OrderID and ProductID in the Order Details table are not primary key fields.  The issue of a combination key is separate from the issue of a join table. Orders is also a join table but it has a single field primary key, OrderID.

  5. @Jonathan Cutting What you are looking at is called the Foreign Key which is the primary key it the originating table. There is one instance where you do see the same primary keys in two tables, thats called a Join Table and that is something different in regards to creating tables with data.

  6. Thanks, but when speaking of the one-to-many relationships, the OrderID and ProductID fields in the Order Details table function as foreign key fields, not as primary key fields. The primary key field is always on the one side of a one-to-many relationship, never on the many side.
    The key symbols beside those fields in the Order Details table indicate that together, they create a combination key for that table. The values in both fields considered together must be unique for every record.

  7. You said the primary key field is never on the "many side" of a relationship. I see two such instances in the table "Order Details." In fact, BOTH of those key fields are on the "many side" of a relationship, and neither of them are on the "one side."

  8. HOLY CRAP!!!! I took this course last semester, and I needed a refresh of everything and as soon as you said think of it as parent and child, it all made sense!!!

    Thank you so much!

LEAVE A REPLY