Understand the basics of electronic databases, the organization of data in tables, the various relationships between tables, and the crucial role of keys in connecting data.
A database is a structure that organizes and stores data electronically. The data is stored using a database management system (DBMS) such as Microsoft Access, MySQL or Microsoft SQL Server. Data is organized into rows, columns, and tables, so that it can be easily accessed, managed and updated.
Tables
A table is a set of data represented by columns and rows. A column is referred to as a field and a row is a combination of column values and is referred to as a record. Tables contain a unique set of characteristics and they store data of the same type in each row.
Data Fields
A data field which is one piece of information you track in your database. Each data field in the table can define the characteristics of its data as a string value, numeric value, date and/or time values.
Relationships
A relational database contains tables that relate to another table by using a relationship. The tables are connected by a common field. The relationships are defined as:
- One to Many: this is the most common type of a table relationship. For every record in Table A, there are multiple records in Table B.
Example: There is a one to many relationship between the Customers table and Orders table. A customer may have many orders in the Order table.
- Many to Many: For every record in Table A, there are multiple records in Table B, and vice versa. In this case, a third table called a Join Table is created to which will contain only unique values
Example: Many to Many relationship between Orders and Products with the table ProductsOrders functioning as the Join Table. The table ProductsOrders holds all the details about each order and what products it contains. Its primary key is a combination of the primary key of the Orders and Products table.
- One to One – the rarest type of a table relationship. It is used for security and organization to avoid empty fields that only apply to some records in a table.
Keys
Key fields are used to build the relationships between data in different tables.
- Primary Key: Usually auto-numbered, unique ID for internal tracking and for matching records between related tables. Each value is unique to the table and cannot be null.
- Foreign Key: A field in a related table pointing back to the Primary Key in another table. For auto-numbered fields it is defined as a number value.
Notes on Primary Keys
- The Primary key should always be the first field in each table, followed by any foreign key(s).
- There is only one Primary Key per table.
- Primary keys should never be actual data – not even something unique like a Social Security number or Student ID. These values are obtained from an outside source and, while they seem unique and reliable, they could produce data entry errors.