Database Relationship Introduction

From SwinBrain

This page expands on Database Introduction to examine how relationships can be created between records in tables. If you are unfamiliar with databases start by reading the Database Introduction then return to this page.

Introduction: The material in this page aims to introduce important concepts rather than provide a detailed examination of the topic.

Contents

Relationships in Data

Often you will want to be able to define relationships between data in your database. Consider the Product Table from the Database Introduction, lets now add details of suppliers. For this we can add a Supplier Table that contains the details of the suppliers, this is illustrated in the table below. The products that exist in the Product Table are supplied by one of these suppliers, this gives us a relationship between the products in the Product Table and the suppliers in the Supplier Table. This relationship can be setup using values to link the two rows, have a look at the example below.

Supplier Table
Supplier Code Supplier
Fred1 Freds Supplies
Stuff We Supply Stuff
... ...
Product Table
Product Code Name Cost Supplier Code
W123-a Widget type 3 $0.25 Fred1
C156-e Large cog $5.40 Stuff
W871 Small Widget type 7 $0.05 Stuff
... ... ... ...

You can have several different kinds of relationships between records in a database. These include One-To-One relationships where one record has a relationship to one other record, One-To-Many where one record has a relation ship with many records for example one Order may have many items on it, Many-To-Many where many records are related to many records.

Tables and Keys

If tables are linked on values, how do we know what we are linking to? This brings us to the concept of keys in the database. To be able to link to an individual record we need something that uniquely identifies the record, in a database this is called a Primary Key. In a database a primary key is a set of fields (one or many) that uniquely identify each row in the database. The database will ensure that these fields are always unique for each row. In the above example the Supplier Code is the primary key for the Supplier Table, if you try to add another row with a Fred1 value in the Supplier Code the database will reject it thereby ensuring that each row has a unique primary key.

To add the relationship from a product to a supplier you add a field to the Product Table that links it to a supplier in the Supplier Table. The value of this field must have a matching value in the Supplier Table. This kind of field is called a Foreign Key. The different kinds of relationships can be implemented as shown below.

  • One-To-One: A record in one table has one, and only one, matching a row in a second table.
  • One-To-Many: A record in one table has many matching rows in a second table. (Very common relationship type).
  • Many-To-Many: A record in one table has many matching rows in a second table, but the second table also contains records that match many records of the first table.

Note: “Matching” refers to a foreign key matching the primary key value of a record in another table.

Entity Relationship Diagrams

Database tables with relationships

Entity Relationship Diagram (ERD) or Model (ERM) can be used to diagrammatically show database tables and relationships. Tables are shown as boxes, table name listed at the top. The boxes contain fields including primary keys, and foreign key fields. Lines and connections represent relationships between the tables e.g. one-to-many.

Links

The following links provide more details on working with databases.

These external links provide more details on entity relationship modeling and relationships in databases.

[edit]Links: Topic Brains
Return to Swin Brain