How do relational databases work?
In: Technology
Let’s say we’re trying to maintain a family tree. We know about people. Every person has a name, a father, a mother, a date of birth, maybe a date of death, and maybe a list of addresses (birth place, places where they lived, burial site, etc.). Imagine writing all this down on a sheet of paper in a table format. Each person has a row, and each thing we know about that person has a column.
How do we deal with those addresses? Each address has a number, a street, a city, a state or province, a country, and maybe a postal code. So maybe five or six columns for each address. If you just record birth place and burial site, that’s ten or twelve columns right there. The table starts getting a little cramped. If we want to track every place the person lived, it gets even worse, and what happens if you lay out the table to keep track of four total addresses, but someone has lived in ten different places?
We might solve the problem by writing things down in two different tables. We have one that lists the people, and we have another where we list all the addresses we know. Then in the table listing the people, we have one column for each address where we use some sort of number or tag to indicate which address we are referring to. That makes the table easier to read. If you don’t need the addresses when you’re reading the list of people, you can just ignore those columns, and they don’t really get in the way. If you need the address, you can look it up in the address table.
It doesn’t solve the problem of how you deal with some people having a lot of addresses, though. So, there’s another approach. You can make a third table. In this one you have three columns. For each person, you have a row for each address we know about them. One column indicates who the person is. One indicates which address we want. And a third column says what the address means “birth place,” “burial site,” “residence”, etc. This approach is more complicated. To find a person’s birth place, you look for the row with their tag on it and the meaning “birth place”. That gives you the indicator for the address. Then you can look up that address in the addresses table. This is more complicated, but it’s more flexible. It lets you track as many addresses per person as you like. This new table, by the way, is called an “association table” because it associates or connects two other table: people, and addresses. Also, it means that you don’t have to have columns for addresses in the table of people, making it a little easier to read.
For all of this to work, these different tables need to have some way of pointing to a specific row in another table. For the association table, it’s no good to write “John Smith” for the person, because there are so many “John Smiths”. There are several ways to deal with this, but the easiest is to add another column to the people table where we write a unique number for each person. Then we can refer to that person by their number. That column is what we call an ID or a “key”.
This is the core of relational databases but on paper. You have more than one table written down, and you have IDs or keys for each row in the tables that you can use to relate two or more tables together. There’s a lot more going on in relational database than this, but this is kind of the heart of it.
Latest Answers