Databases

Analysis, decision-making, customer journey, they are everywhere.
Reading time : 5 minutes

👋,

Welcome to The Tech Guide.

I am Nicolas GAUTRON, freelance web developer.

It would help me a lot if you could share the subscription link.
The more of you there are, the more I will attract sponsors, ensuring this newsletter remains free ❤️.


Computer systems need to keep in memory a plethora of information. Customer accounts, subscription status, or even simply the website's content.

These pieces of information are stored via software called "database management systems." A large-scale website utilizes multiple database technologies, each specialized for a particular use. Among others:

  • Working with huge batches of data.
  • Or conversely, on a single data point.
  • Search engine.
  • Identifying relationships.
  • Storage of documents and media.
  • Ultra-fast access.

Today, we will focus on relational database management systems. These are the technologies most commonly used by developers in their daily lives. You might have already heard of MySQL, Oracle, SQL Server, or PostgreSQL.

Let's build a newsletter 💪

Imagine a database as an Excel file.
A tab in the file represents what we call a "table".
A column in a table is called a "field".
A row in a table is called a "tuple", "entry", "row", or "record".


For example, if you want to model in a database this newsletter, the wonderful people who have subscribed to it, and the mail sendings:


Table: edition

identifier title publication_date content
databases The Tech Guide #5 - Databases 2024-01-05 Welcome to…
frontend-backend The Tech Guide #4 - Front-end and Back-end 2023-09-10 👋, welcome…

Table: subscription

identifier email
1 foo@bar.com
2 hello@world.com

Table: sent_mail

edition_identifier subscription_identifier send_date
databases 1 2024-01-05
databases 2 2024-01-06

You get two tables that represent concrete objects: edition and subscription. Each line is manually or automatically assigned a unique identifier. It's called a "primary key".

The third table, sent_mail, uses these keys to relate editions and subscriptions to each other. It manipulates "foreign keys".

This way of organizing data explains the name "relational database".

SQL

To manipulate these tables, we use Structured Query Language.

SQL, pronounced S.Q.L and formerly known as sequel, aims to be a natural language close to English. It very easily allows writing queries to carry out the 4 basic operations of data processing:

  • Creation. Example: someone subscribes to the newsletter.
  • Reading. Example: check who has already received the edition to avoid sending it twice.
  • Updating. Example: correcting a typo in the content.
  • Deletion. Example: unsubscribing from the newsletter.

For example, to obtain the details of this edition: SELECT title, content FROM edition WHERE identifier = “databases”

Easy, isn't it? 😉
We can do crazy stuff in SQL, but the basic syntax is really accessible to everyone.

It's exactly this query that is played if you display the page https://en.the-tech.guide/post/databases/. The URL is analyzed to extract the edition's identifier. It is then injected into the SQL query allowing to construct the page.

The database serves in this case to code only one page and to automatically generate all the variants.

😱 And if someone tampers with the URL hoping to modify the underlying SQL query?

That's called SQL injection. It's the flaw exploited in 90% of website hacks.
Every use of data public to users must be secured. Every day, robots randomly try thousands of injections on the most known sites.

NoSQL

Relational databases, despite their power, show limits on large volumes of data.

The advent of Cloud and BigData has seen the emergence of new technologies categorized as “NoSQL”.

Broadly, NoSQL refers to databases that are not relational. It’s a super broad designation encompassing a whole range of technologies.
Fun fact: NoSQL technologies often use SQL or a derivative 🤪. They tried to make up for this poor naming with Not Only SQL.

In a NoSQL database, data is organized completely differently, at least in the internal engine of the software. This allows for easier distribution across different machines and thus infinitely increases computing power and available memory.

That's all for today 🖖.


Did you enjoy this content? Then so will your friends!
Don't forget to share the subscription link.


Questions, comments, encouragement? Don't hesitate to send me a message on Linkedin or directly in the comments of this edition.

See you soon.

😘