How do relational databases work?
In: Technology
A full answer to that question is pretty much a book in itself, and there are plenty introductions all over the web. It would help if you could be more specific. Is there something in particular you want to know?
Lets give it a try: A relational DB is a way for storing and accessing data. It is based on “relational algebra”.
Most commonly, a data model is designed first, then data is deconstructed by the process of “normalization”. This aims to turn your data in a basic form like into separate tables. E.g. one table for customers and one for orders.
The so called “keys” are important so you can “join” the data again and make a report that answers questions like “what did each customer order?”. One benefit comes from you not needing to store all those “answers” and reports permanently, but rather modify your “query”.
So why not put it in a single table then you ask? Because the normalized data is more flexible and eliminates waste like storing same data multiple times. It’s one of the key features of RDBMS, which was invented around the 70’s where hardware was very limited.
The language to query the data is called SQL; there are variations to this like T-SQL or PL/SQL. An RDBMS is the system/software you choose e.g. SQL Server or Oracle.
So taking the example, when an RDBMS contains the pure facts about customers and orders, it can say “ask me anything about the customers and orders and i can answer them for you”. This can include something basic like “how many customers do we have?” to more complex ones like “what is the most ordered item and by which customer?”
Source: I’m a DBA and developer.
Basically all the important “things” you identify get a table, which you can imagine like a glorified spreadsheet. A Person. A Job Position. A City. You can then have columns for each piece of information you care about for each “thing” you have. You could keep track of each person’s age for example. But something else interesting happens here. You can also keep track of their children (other Persons), their job position (a Job Position), and their city of residence (a City). These “things” exist as records in another table. So you give each record a unique identifier called a “key” so you can use it to refer to only that thing (i.e. you wouldn’t want to use someone’s name as a key since many people can share the same names) anywhere else in your database so that when you’re asking for something specific (e.g. I want to see every Person who worked this specific Position and lived in this specific City), it can give you a clean answer. If you simply let someone write down their city otherwise, you’d get problems like, maybe they wrote St Paul instead of Saint Paul, or something like that, and it gets much messier trying to find information you want.
They work by storing data based on its “relation” to other bits of data. A classic example is the student database. A table contains student data, each student attends a class, so there is a table of class data, each class is contained within a course, so there is a table for course details. Students are also enrolled in a course so there is a relation there as well (the example can be extended far beyond this simplified version). These relations/connections are maintained by keys that link the tables together.
Source: it’s been 7 years since I studied database design lol
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