Why a database
Problem
The spreadsheet everyone touches
A small online shop keeps its orders in one big Excel sheet. Each row has the date, the customer's name, email and city, the product, the quantity and the price. After a year there are 3,000 rows and the trouble starts:
- Maria changes her email. It is on 40 rows. You fix 38.
- A colleague types “Sofa”, you type “Sofia”, and the report by city is now wrong.
- Two people open the file at the same time. One of them loses their changes.
- “How much did each customer from Plovdiv spend in March?” takes half an hour of filters.
Excel is a good tool, but it was not made to keep the data of a running business.
The idea
A database
A database is an organised place for data. The program that looks after it is a database management system (DBMS). It does four things a file does not:
- It keeps rules. The email is unique, the price is not negative, every order belongs to an existing customer. A row that breaks a rule simply does not get in.
- It keeps each fact once. Maria's email is in one place. Her orders only point to her.
- It lets many people work at once, without anyone losing their changes.
- It answers questions, even over millions of rows, usually in milliseconds.
You will meet the names PostgreSQL, MySQL, SQL Server, Oracle and SQLite. SQLite is in your phone, in your browser and in many apps, and in this course it runs right in the page.
More precisely, if you are curious
PostgreSQL, MySQL and SQL Server are servers: a separate program that applications connect to over the network, often from many computers at once. SQLite is a library: the database is a single file that the program opens itself. The language you ask them questions in is almost the same. You will meet the differences along the way.
The idea
Tables, rows, columns
In the most common kind of database, the relational one, data lives in tables. A table is for one kind of thing: customers, products, orders.
- Each row is one thing: one customer.
- Each column is one property: name, email, city. All values in a column are of one kind.
These are the customers of the shop you will work with throughout the course. Press “Run”:
What you see is a query in the SQL language. SELECT * means “all columns”, FROM customers means “from the customers table”. The semicolon ends the query.
The field is a real database. Change the query, run it again, make a mistake on purpose: nothing can break, and “New database” puts it back to the start.
More precisely, if you are curious
Look at customer 12, Boris. Instead of a city there is NULL: “no value”. It is not empty text and not zero. It gets a whole lesson, because NULL is behind many wrong reports.
Try it
The products
Show all the shop's products. Their table is called products.
Solution
SELECT * FROM products;25 products. Look at the category_id column: it does not say “Dairy” but a number. The number points to a row in the categories table. That way each category's name is written once.
Try it
Row or column?
Exercise
A walk around the shop
Besides customers and products the database has four more tables. Look at two of them.
1. Show all orders (orders). How many are there? Look at the customer_id column: it says whose order it is, with the same number the customer has in customers.
All orders:
Solution
SELECT * FROM orders;60 orders. Order 60 belongs to customer 1. Open the customers above and see who that is: Maria.
2. Show the categories (categories). Some have a parent_id, two do not. What do you think that means?
All categories:
Solution
SELECT * FROM categories;“Food” and “Home” are the top categories (no parent). “Bread and pastry” has parent_id 1, so it sits under “Food”. That is how a tree is kept in a table.
Test