Tables, Rows and Columns

A table holds one kind of thing. A customers table holds customers. A products table holds products. Mixing both into one table is the first design mistake people make, and every later lesson gets harder if you do.

Inside a table, a row is one of those things — one customer. A column is one fact about every thing in the table, such as the city. Where a row and a column meet you get a single value.

The important difference between a database table and a spreadsheet column is enforcement. In a spreadsheet, a column called Price is a heading. Someone can type "call for quote" into it and the file accepts it.

In a database, declaring that column as a decimal number means the database refuses anything that is not a number. The refusal happens at the moment of the bad write, so you find out immediately rather than discovering it three months later when a report fails to add up.

CREATE TABLE customers (
    customer_id   INT           NOT NULL,
    company_name  NVARCHAR(120) NOT NULL,
    contact_name  NVARCHAR(120) NULL,
    email         NVARCHAR(200) NULL,
    city          NVARCHAR(80)  NULL,
    country       NVARCHAR(80)  NOT NULL,
    credit_limit  DECIMAL(12,2) NOT NULL,
    created_at    DATETIME2     NOT NULL
);

• Each line is a column: a name, a type, and whether it may be missing.
• INT stores a whole number. NVARCHAR(120) stores up to 120 characters of text in any alphabet. DECIMAL(12,2) stores up to 12 digits with exactly 2 after the decimal point. DATETIME2 stores a date and a time.
• NOT NULL means the value is required. NULL means it may be absent — see the next section for why that distinction matters more than it looks like it should.
• company_name is NOT NULL because a customer without a name is not a usable record. contact_name is optional because you may genuinely not know it yet.

NULL is not zero and it is not an empty string. It means no value is recorded. A credit limit of 0 says the customer may not buy on credit. A credit limit of NULL says nobody has decided yet. Those are different facts and storing them the same way loses information.

Treat every nullable column as a question you will have to answer in every query that touches it. That is a real cost, so make a column NOT NULL whenever the value is genuinely always known. Filtering on nulls gets a full lesson later in this course.

CREATE TABLE products (
    product_id     INT           NOT NULL,
    product_name   NVARCHAR(150) NOT NULL,
    category       NVARCHAR(60)  NOT NULL,
    unit_price     DECIMAL(10,2) NOT NULL,
    units_in_stock INT           NOT NULL,
    discontinued   BIT           NOT NULL
);

CREATE TABLE employees (
    employee_id INT           NOT NULL,
    first_name  NVARCHAR(80)  NOT NULL,
    last_name   NVARCHAR(80)  NOT NULL,
    job_title   NVARCHAR(100) NOT NULL,
    hire_date   DATE          NOT NULL,
    manager_id  INT           NULL,
    salary      DECIMAL(12,2) NOT NULL
);

CREATE TABLE orders (
    order_id     INT          NOT NULL,
    customer_id  INT          NOT NULL,
    employee_id  INT          NULL,
    order_date   DATE         NOT NULL,
    status       NVARCHAR(20) NOT NULL,
    shipping_fee DECIMAL(10,2) NOT NULL
);

CREATE TABLE order_items (
    order_item_id INT           NOT NULL,
    order_id      INT           NOT NULL,
    product_id    INT           NOT NULL,
    quantity      INT           NOT NULL,
    unit_price    DECIMAL(10,2) NOT NULL
);

• order_items stores unit_price again rather than reading it from products. That is deliberate: it records the price actually charged, so a later price change does not rewrite history.
• employees.manager_id is nullable because somebody at the top has no manager.
• These tables have no keys or links yet. The next two lessons add both.