Creating a Table and Choosing Data Types

Every table you have used so far, users, posts, products, had to be created by someone at some point. Now it is your turn. You create a new table with CREATE TABLE, and while you do it, you decide exactly what columns it will have, and what kind of value each column is allowed to hold.

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  price DECIMAL(10,2)
);

Common data types you will actually use

• INT: a whole number, good for an id, a quantity, or an age
• VARCHAR(n): short text up to n characters, good for a username or a product name
• TEXT: long text with no strict limit, good for a blog post body or a long comment
• DECIMAL(10,2): a precise number with decimal places, ideal for money, since it avoids the rounding mistakes that plain decimals can cause
• DATE or DATETIME: a calendar date, or a date with a time attached, good for created_at or a birthday

Choosing the right data type is not just a technicality. It stops mistakes before they happen. If price is DECIMAL, the database will not let someone accidentally store the word "free" in it. If created_at is a DATE, you can reliably sort posts by when they were written.

Optional extra rules

You can also add small rules onto a column. NOT NULL means the column can never be left empty. DEFAULT gives a column a starting value when nothing else is provided:

CREATE TABLE comments (
  id INT PRIMARY KEY,
  body TEXT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Designing a table on paper first

Before creating a real table, it helps to sketch it out: what should this table be called, what columns does it need, and what type does each one deserve? For a users table, you might land on id, username VARCHAR, email VARCHAR, and created_at DATETIME.