SQL Database structure | Schemas | Data Normalization

SQL Database structure | Schemas | Data Normalization

So let’s start with SQL, now SQL is not a database, SQL stands for structured query language and in the end, SQL is a language that allows you to write database queries.


SQL introduction

SQL Database structure | Schemas | Data Normalization

The commands are CREATE, ALTER, DROP, RENAME, and TRUNCATE. Data Manipulation Language (DML) – These SQL commands are used for storing, retrieving, modifying, and deleting data.

These Data Manipulation Language commands are: SELECT, INSERT, UPDATE, and DELETE. There are more commands to just deselect command but this is how you create such queries.

You have certain keywords certain syntax features like SELECT keyword or the FROM keyword and you add your own data like SELECT which fields from which table.

In this example, here so that you can retrieve the data from the database you need and there are not just commands for retrieving data there are all the commands for inserting, updating or deleting data, for creating new tables, for joining tables If your data is distributed across multiple tables.

So, there are a bunch of commands which you can combine, it’s quite powerful this is SQL.

SQL Database Structure

Now often when we think about SQL vs NoSQL, we, of course, think about the databases that are behind that. So the database we’ll get the data from with the SQL language here or with the structured query language.

The database we typically use is a relational database and that means we have a database which works with certain assumptions or in a certain way and it supports the SQ language.

The structured query language such a database works with tables something like a product, table. For example, if we want to store the products of our app we could do that in a products table.

So a table is like a data bin a storage container, now in SQL world where we query data with SQL, we will have very strict requirements for the data.

we store in our database tables, do you be precise with a clear schema of which data can go into a table and this schema defined by so-called fields.

So in our products table, we would have, for example, these fields an ID, a Name, a Price, and a Description and every new entry.

We add every new record as it is called every new row in our table is. Well, a record that has values for these fields and the important thing is, it can’t have more fields than the ones.

We defined for the table, so it’s not possible that one record has like a name or price in the description and the next record also has a name, price, description and some extra data like a tax.

You can add this by adding a tax field but then all the entries or all the records need to have tags. They might be null, they might be empty but you need to provide some information for that tax field and if it only is that there is no information.

Schemas and Data Normalization

So you have a clear schema and this is how a database table looks like in a SQL world, now this is a database table and that schema thing is really important all records in a table have to adhere to this schema.

When we put data into such a table we, therefore, have to normalize it, which means we have to ensure that. However, we are fetching the data and whichever extra data we might have or whichever data might be missing that we bring it into a format which fits into that table.

That’s one important building block of a SQL world.

Relations

The average building block is that you typically don’t only work with one table but instead with multiple tables which are related.

So we could have users of products in the orders table we would not have one orders table only which then holds all the products and users information per record.

Instead, we would split that up, we would have a user’s table with its fields and then we have some entries in there and the same for the products.

So we would have something like this where we manage users and products separated from each other, but of course, there is a connection.

For example, a product could be created by a user or in this example here we could have orders. Order simply is a combination of a product and a user because one user might have many orders and one product might also be part of different orders of different users.

So the orders table would be our connection here it would be setting up the
relations, we would have a user ID gene a product ID field in there and therefore we can connect a specific user to a specific product with the product which was ordered by the user.

This is very typical for the SQL world and data relational databases which use SQL and therefore we got two important characteristics already the strict schema and that relation world.

Types of Relations

By the way, there are multiple types of relations in a SQL world we just saw the many-to-many relation obviously we also get a one-to-one relation now.

One-to-one relation

Here’s an example we could have a user and we could have a table with contact data, the user may be is made up of ID, a name, age, and the contact data could be also an ID.

Typically every record has an ID in every table but besides the ID, a contact data field. Contact data record could have the email and phone number fields.

And then maybe we want to separate it and two tables, but we still want to ensure that every user has one contact data piece, and that can be done by adding a contact data ID field to the user which holds the ID of the contact data in the contact data table which belongs to that user.

And that would be one-to-one because one user has one contact data entry or record and one contact data record belongs to one user. You could also add a user ID field to the contact data table and point at the user to which it belongs.

One-to-many relation

We can also have one-to-many and strictly speaking, our one-to-one year what do you want to many, because since we don’t assign a user ID to each contact data.

Theoretically, one contact data could belong to multiple users, here’s another example, one-to-many would be pretty much the same now with users and products.

We have a creator ID on the product point and the user who created that product and therefore this product only belongs to this one user, but when a user can be part of multiple products.

And then we, of course, we got many to many, many to many are the case. We already saw we got two tables users and rows. Let’s say a row could be something like admin editor stuff like that.

And we want to connect these two tables now since a user can have multiple rows and a row can be assigned to multiple users.

There’s no way we store that information in two tables, only because we would have to add one new field so, one new column for every row.

We want to assign to a user and the other way around and since we can’t predict how many rows a user will have and how many users a row will be assigned to you.

We add an extra table in between user, rows table, this table has a user ID and a row ID field, and therefore it creates as many connections as we need that’s a many-to-many relation.

This is not about learning, all about SQL, it’s about learning the key characteristics and that’s the strong schema and the relational nature of the data.

We store data distributed across multiple tables which we then connect through relations. And the SQL language to structured query language is capable of querying these relations.

There are special commands so called joins which you can use to retrieve connect the data in one result set even if it is stored in multiple tables.

Obviously, the more complex the relation is and the more tables are included the longer such an operation can take.

But it is possible and it is one of the core strengths of the SQL world, the fact that you have normalized distributed data which can merge together with SQL queries now that was the SQL world.


Leave a Reply

Your email address will not be published. Required fields are marked *