Table Design

When designing a table, consider the following steps:

• Pick a single subject that your table is going to be about
• List all of the information that you are going to need to know about this subject
• From the list, break things apart into the pieces you will need to query the table

Field Design

A field should not contain several values of the same type of data

• Don’t do a favorite foods field that stores data like “pizza, tacos, apples”

A table should not have multiple fields with the same data

• Don’t do multiple fields with names like favorite_food_1, favorite_food_2, favorite_food_3, etc.

Atomic Fields

Make the fields atomic, which means that they are the smallest piece of data that can’t or shouldn’t be divided further

• Note that this is dependent on your usage (address could remain as number + street in most circumstances or you could break it further into a field for the number and a field for the street if you are doing geographic information systems work)
• Don’t break data down further than you have to for your usage

1NF

Our tables are considered to be in first normal form (1NF) if both the following are true about the table:

• Each row of data contains only atomic values
• Each row of data has a primary key

However, we often can’t do a database with a single table. Often, when we normalize our initial table, additional table creation will be required

• In the favorite food example, we would end up with a second table of favorite foods that has a relationship to the person table

The DDL category of SQL statements offer the capability of creating, altering, and dropping (deleting) tables. ::::{.left} * CREATE TABLE - creates tables * ALTER TABLE - alters existing tables * DROP TABLE - removes an existing table ::::

CREATE TABLE tablename (
    fieldname1 datatype constraints,
    fieldname2 datatype constraints,
    fieldname3 datatype constraints
)

Create Table Example

CREATE TABLE tasks (
    task_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    start_date DATE,
    due_date DATE,
    status TINYINT NOT NULL,
    priority TINYINT NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)

Constraints

PRIMARY KEY CONSTRAINT

Indicates that the field can be used as a unique identifer for rows in the table (automatically unique and not null)

FOREIGN KEY CONSTRAINT

Specifies that the value of a field must match the values appearing in some row of another table. This is how we can provide referential integrity between two related tables.

CREATE TABLE checklists (
    todo_id INT AUTO_INCREMENT,
    task_id INT,
    todo VARCHAR(255) NOT NULL,
    is_completed BOOLEAN NOT NULL DEFAULT FALSE,
    PRIMARY KEY (todo_id , task_id),
    FOREIGN KEY (task_id)
        REFERENCES tasks (task_id)
        ON UPDATE RESTRICT ON DELETE CASCADE
);

CHECK CONSTRAINT

Makes certain value satisfies a boolean expression (must be true)

CREATE TABLE products (
    id serial PRIMARY KEY,
    name text,
    price numeric CHECK (price > 0)
);

NOT NULL CONSTRAINT

Specifies that the field may not contain a null value (a value must be specified for the field)

CREATE TABLE products (
    id serial PRIMARY KEY,
    name text NOT NULL,
    price numeric CHECK (price > 0 )
);

UNIQUE CONSTRAINT

Ensures that the data contained in the field is unique amongst all rows in the table

CREATE TABLE users (
    id serial PRIMARY KEY,
    username NOT NULL UNIQUE,
    password NOT NULL
);