Relational databases are a collection of tables, columns, and rows to store information. They also use indexes to identify specific rows within the database. An index is a unique identifier given to a row which can be used to identify a specific record. Relational databases use the unique indexes of records to link them to other records within other tables in the database.
Let's say we have one table in our database that stores people and another table that stores addresses. We can then link the people entries to the address entries using the unique indexes. We would want to store the addresses in a separate table, rather than in the same table as the people, as multiple people may share the same address. If we stored the addresses in the user table then we would have to duplicate the information, whereas storing them in a different table and linking them we only have to write it once. To further understand why this is important, imagine you want to update an address for multiple people, if the address is stored individually within the same table then you have to update it multiple times, whereas if the address is stored in a different table then you only have to update it once.
The below table shows a table of people, with four columns. The first column is the unique identifier for each person, the index. The next two columns are person-related data, their first names and last names. The last column is a linkage column, that links this table to an Address table. We have three people within this table, each has three unique indexes and indexes linked to addresses that are not unique. We can see from this table, without even looking at the Address table, that two people share the same address and the last person has a different address.
| Id | FirstName | LastName | AddressId |
|---|---|---|---|
| 0 | John | Smith | 0 |
| 1 | Jane | Smith | 0 |
| 2 | Bob | Johnson | 1 |
The below table shows a table that contains information related to addresses. This table has four columns, the first one is the unique index and the next three are address related information (street, city, and postcode). Notice how we don't have an extra column linking this table to the person table above, it is not needed. We only need to link the table in one direction, as that is enough to be able to find related information. For example, we could use the 'AddressId' on the Person table to find which address the person is related to, or use the 'Id' index of the address table to find what people are in that address.
| Id | Street | City | Postcode |
|---|---|---|---|
| 0 | Main Street | Franklin | AB12CDE |
| 1 | High Street | Smith | FG34HIJ |
Relational databases also contain the concept of primary keys and foreign keys. These keys refer to the indexes used when creating relationships between tables. The primary key is the index used to create the relationship and the foreign key is the unique index on the other table. In the example above the primary key would be the index within the 'AddressId' column of the people table and the foreign key is the 'Id' column of the address table.
This article was originally posted on my website: https://acroynon.com/
Top comments (0)