Most databases are made up of many tables stored in a single file.  Each table contains a logical grouping of information with its own records and fields.  When using multiple tables within a database, the tables must have some common fields to allow cross-referencing of the tables.  The referral of one table to another via a common field is called a relation.  Such groupings of tables are called relational databases.

Relational databases allow us to store vast amounts of data with far simpler maintenance and smaller storage requirements than the equivalent flat database.  As an example, say we had a flat database listing products stocked by a grocery store with several fields describing each product’s manufacturer (manufacturer name, address, phone, …).  If you have 1,000 products made by the same manufacturer, there is much repetition of information in the flat database.  And, if the manufacturer changed their phone number, you would have to make that change in 1,000 places!  In a relational database, you could use two tables, one for products, one for manufacturers.  In the product table, you would simply have a manufacturer ID that would correspond with an ID in the manufacturer table (a relation), which would have that manufacturer’s information.  Then, if the phone number changed, you would only have to change one field in the manufacturer table - quite a savings in work!  When you break down database tables into simpler tables, the process is known as database normalization.

Relations among tables in a relational database are established using keys.  A primary key is a field that uniquely identifies a record so it can be referenced from a related table.  A foreign key is a field that holds identification values to relate records stored in other tables.

When one record in one table is linked to only one record in another table, we say there is a one-to-one relation.  When one record in one table links to many records in another table, we say there is a one-to-many relation.  And, when many records in one table are linked to many records in another table, we say there is a many-to-many relation.