Tutorial on Database Schema - Slide 5 - Primary and Foreign Keys and Relationships.
Back Next Slide
- Keys
- Relationships
- One-to-Many and Many-to-Many Relationships
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).
'Crows-Feet'
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