understand and know how tables work in database It is essential to be able to advance in learning how to use it and later understand its administration through the SQL language. For this reason, at SrCodigo we have decided to dedicate this article to explaining, in detail, the database table bases.
It seems to us essential for a complete learning process and without beating around the bush, focus on the following points:
-
Simple answer toWhat is a relational database table?
-
Introduction to what types of columns in a tableto in database.
-
Basis of the relationships between tables.
Content
- What is a database table?
- Columns of a table and their types
- Relationships between database tables
- And how are these relations between tables made?
- Now it’s time to create tables with SQL
What is a database table?
A database table It is the main and most general structure that exists to store information. Thus, a table could contain information about police officers at a police station, students at a high school, or computers at a repair company.
A database will contain one or more tables, without any kind of limit regarding the number.
Each table will be identified with a unique name that cannot contain blank spaces or special characters such as accents or hyphens.
In addition, the information or data that is stored in a table is structured, so that they are separated from each other with a clear logic.
Specifically, a table is divided into rows and columns:
- Rows are the records in a database.also called tuples (although in SrCode we always call them RECORDS, there are no more.
- Each row will have its information divided into columns with a unique name within a table. One column could be a police badge code or the name of a video game.
You will have noticed that the definition of a database table It looks a lot like a regular table, like those in Excel. So I recommend that you start looking at them like this, since their structure is very similar even though technically they are not exactly the same.
In the following image, to make it easier for you to digest what I have explained, you can see the data stored in a simple table where comics are stored:
Columns of a table and their types
the columns of a table define the data that a row can store. These columns, once defined, will be the same for each of the records in the table to which they belong. A police table can NOT have a row with “badge code” and “name” and in another row of that same table the “name of the partner” and the “office where he works”.
Thanks to the columns of a table we can create records (rows) with the structure we need to store any type of information. For example, an Animal table, from a Zoo, could have the columns code, name, breed, subtype and enclosure.
The columns of a table can be of different types. We have configuration values to store numbers, text, decimals, dates, etc. You can see this in a detailed and entertaining way in another tutorial that we will propose later.
Continuing with the previous example, we could define the “name”, the “breed” and the “subtype” of the animal as type text, while the “code” and the “enclosure” would be identifying numerical values.
Finally, you should know that each column may have restrictions to define what we allow and what we do not allow to store in them. Always with the idea of having control over what that store our tables in database.
Beyond the technical commands in SQL to carry out all the comments, there is no more terminology to learn.
Then, as a curiosity, I leave you a photo of any WordPress table and the definition of its columns:
Relationships between database tables
Although in this article I am not going to go into detail about all the functionalities, you should know about this unique characteristic of databases: relationships between tables. From here, precisely, the name “relational databases” arises.
He goal of relationships between tables is to store complex information and in turn easier to understand and maintain. The limits are set by the experience, creativity and knowledge of the database designer.
And how are these relations between tables made?
The relationships between tables are made through its columns. A column in a table that handles the relationship between it and another table will have to match the type and value of the column in another table.
Imagine that we have two tables, one for patrol cars called “coche_patrulla” and another to store the records of police officers called “agent_policia”. A patrol car must have the police code to which it is assigned, therefore, it would have a “police_code” column that will match the “code” column of the police table. Thus each car ( record of the table car_patrol ) will have the code of the record of the agent_policia table to which it is assigned.
Now it’s time to create tables with SQL
I am sure that if you have read this tutorial calmly, you will have understood the concept of a table in a database.
And how should you continue learning now? Well, you know what they are for and how the tables of a real database workso you can start learning what are the commands or SQL statements to create tables and columns.