Tutorial on Database Schema - Slide 5 - Primary and Foreign Keys and Relationships.

Back   Next Slide

  1. Keys
    • Primary and Foreign Keys
  2. Relationships
    • One-to-Many and Many-to-Many Relationships
Ordering in Starbucks

I would like to start by defining some basic conventions.
Primary Keys.
  • These are indicated by 'PK' alongside the field in a Table.
  • They show the field that identifies the record uniquely.
  • For example, the 'product_id' for a Product and an 'order_id' for an Order.
  • The values are usually generated by an 'Auto-Increment' field.
  • They don't have any intrinsic meaning and are simply integers generated automatically one after another.

    Primary/Foreign Keys.
  • These are indicated by 'PF' alongside the field in a Table.
  • In this case, the field also exists as a Primary Key in another Table.
  • In our example, the Primary Key in the 'Customer_Orders_Products' Table is a Composite Key, made up of two Fields.
  • The 'product_id' is the Primary Key in the 'Products' Table, and the 'order_id' is the Primary Key in the 'Customer_Orders' Table.

    Foreign Keys.
  • These are indicated by 'FK' alongside the field in a Table.
  • They show a reference to the Primary Key for a record in another Table.
  • For example, the 'customer_id' in the Customer_Orders Table is linked to a 'customer_id' in the Customer Table (not shown).

    One-to-Many Relationships.
  • One-to-Many Relationships are very common in the real world, and can be read that 'One' Parent can have 'Many' children.
  • In this diagram, One Product can be associated with Many Customer_Orders_Products.
  • The line between Products and Customer_Orders_Products is a continuous line (not a dotted line).
  • This means that the 'product_id' field is a Primary Key in the Customer_Orders_Products Table.
  • A dotted line would indicate the existence of a Foreign Key (which is defined above).
  • At the Customer_Orders_Products end, there is what's called a 'Crows-Feet' symbol.
  • This indicates the 'Many' aspect of the relationship.
  • The little 'o' shows that this is an Optional relationship.
  • In other words, not every Product has an associated record in the Customer_Orders_Products Table.
  • This applies, of course, to Products that nobody orders.
  • At the other end of the line there is a small horizontal line. This means that this is essential.
  • In other words, every product_id in the Customer_Orders_Products Table must match a product_id in the products Table.
  • This means that a Customer cannot order a Product that does not exist.
  • In passing, let me say that this is different from the Product not being in stock.
  • This aspect is called Inventory Control and is covered by a different Database Schema.
  • The Relationship between Customer_Orders and Customer_Orders_Products shows a short horizontal line at each end.
  • This indicates that every Order must contain at least one Product.
  • It also indicates that Product in every Customer_Orders_Products must be associated with an Order in Customer_Orders.

    Many-to-Many Relationships.
  • Many-to-Many Relationships are very important in the real world, and we see them all around us every day.
  • In our example, many Customers can order the same Product.
  • Many Products can be ordered by the same Customer.
  • This is called a Many-to-Many Relationship. It cannot be implemented directly in a Table in a Relational Database.
  • The solution is to create an intermediate table that records all combinations that exist of Customer and Product.
  • For example, if a Customer ordered three Products, then there would be three records for that Customer in this intermediate table.
  • The combination of the Primary keys in the parent Tables become the Primary key in this intermediate table.
  • We also need to allow for Comments like 'Make it extra strong, please'.
  • If we get the structure to the Database right, then it's straightforward to extend it.

    © DataBaseAnswers.org 2006