Before we begin creating our database, we can get a simple understanding of what a database is in the first place.
A database is basically a collection of organised and categorised data that can be accessed and manipulated in a variety of ways in order to meet the purpose of data collection, storage, and processing.
These days, many digital databases are responsible for the storage of many different kinds of information – your employer, for example, will keep a record of your details both personal and those related to your employment, such as an employee number.
Databases generally have other useful functions outside of storage, including the ability to “query” the database and return data that fits a certain set of criteria. Reports can also be created to display and print data based on criteria specified and, in most database solutions, can be used to generate reports based on queries as well.
A database is a useful and powerful tool whenever it comes to data that needs to be organised – or in cases where there are large amounts of data that requires processing or storage, in this case, a database solution is the better choice in comparison to, for example, a spreadsheet which is more useful for smaller levels of data records or specific purposes.
To help a database recognise individual records, database design uses “keys” in order to specify unique data or to link related tables together within a database (we will learn about these relationships later).
To do this, the general rule of thumb is that each table should have a “Primary Key”. A primary key is assigned to a field, or group of fields in some cases, in order to signify it as a unique, identifying field within a table.
The data in the primary key field is always unique to the record it is part of. This means that the database can identify an entire record when running queries or creating reports based on the contents of the primary key alone.
This also allows cross-reference of entire records in a different table using only the primary key – this is called a “foreign key”.
For example, if we are referencing a customer in relation to an order they made, we would reference the primary key in a customer table within the order table.
The customer reference field in the order table would become a “foreign key”, as a foreign key is a key that relates to the primary key of another table. These concepts allow databases to identify and appropriately deal with records and relationships in a logical way.
Databases, specifically relational databases, are able to put in place special “relationships” which can be used to link tables together, allowing information to be cross-referenced and called from one table to another.
A relationship can be set in order to link two tables together by using a foreign key field that relates to the primary key of the field you wish to use other information from. A Customer Reference field in the customer table, our primary key, will relate to the Customer Reference field in our order table, the foreign key, for example.
There are three main kinds of the relationship as follows: