Databases, Tables, Rows and Columns

Four words come up on every page of this course. Learn them once.

Think of a spreadsheet application:

The hierarchy

1
2
3
4
5
6
7

MySQL server
└── database: shop
    ├── table: customers
    │     ├── columns: id, name, city
    │     └── rows: one per customer
    ├── table: orders
    └── table: products

One MySQL server can hold many databases. Each database holds many tables. Each table holds many rows. Each row has a value for each column.

A table you can see

Here is a small table of students. We will create tables properly later. For now, just look at the result.

1
2
3
4
5
6
7
8
9
10
11
12

CREATE TABLE students (
    id INT,
    name VARCHAR(50),
    city VARCHAR(50)
);

INSERT INTO students (id, name, city) VALUES
    (1, 'Asha', 'Pune'),
    (2, 'Ben', 'Delhi'),
    (3, 'Chitra', 'Kochi');

SELECT * FROM students;

1
2
3
4
5
6
7
8

+------+--------+-------+
| id   | name   | city  |
+------+--------+-------+
|    1 | Asha   | Pune  |
|    2 | Ben    | Delhi |
|    3 | Chitra | Kochi |
+------+--------+-------+
3 rows in set

• The table is students.
• It has three columns: id, name and city. The columns define what kind of information every record holds.
• It has three rows. Each row is one student. The second row is Ben from Delhi.

Columns define, rows hold

The columns are decided once, when you design the table. Rows come and go as data is added, changed and deleted. Adding a new student adds a row. Adding a new kind of information, such as an email address, adds a column.

Where this matters in practice

• Use one database per application (for example shop for a shop). Do not mix unrelated applications in one database.
• Use one table per kind of thing: customers in customers, products in products.
• A row should describe one thing. A column should hold one fact about it.