Designing and Managing Tables in MySQL

In MySQL, database design consists in defining the structure of tables, including their columns, data types, primary keys, and relationships with other tables.

The goal of this process is to help avoid data redundancy, maintain data integrity, and improve performance by facilitating data retrieval and manipulation.

Modelling tables

Database design is usually divided into three table modeling stages:

1. Conceptual models
2. Logical models
3. Physical models

These models help in abstracting and organizing data structures, relationships, and the actual implementation.

The conceptual model

The conceptual model, which is the highest level of abstraction, focuses on defining the overall structure and content of the database without including specific details like data types or keys.

Its goal is to capture what data needs to be stored and how different entities are related to each other.

For example, the conceptual model would define:

1. The main entities, for example, Employees and Contracts.
2. How they relate to each other, for example, an Employee is assigned to a Contract.

The logical model

The logical model is more detailed than the conceptual model and focuses on the database structure.

Its goal is to define how data is organized in columns, normalized, and how relationships are managed using more precise rules like primary and foreign keys.

For example, the logical model would define:

1. The Employees entity as the employees table:

   +-------------+----------------+
   | Column name | Column type    |
   +-------------+----------------+
   | id          | integer        |
   | first_name  | string         |
   | last_name   | string         |
   | department  | string         |
   | hire_date   | date           |
   | salary      | integer        |
   | manager_id  | integer        |
   +-------------+----------------+
   

   Where:

   • The id column is automatically set by the database when inserting a new record.
   • The manager_id column references the id column of a record in the same table.

2. The Contracts entity as the contracts table:

   +---------------+----------------------------------------+
   | Column name   | Column type                            |
   +---------------+----------------------------------------+
   | id            | integer                                |
   | contract_name | string                                 |
   | employee_id   | integer                                |
   | start_date    | date                                   |
   | end_date      | date                                   |
   | status        | 'Active', 'Completed'                  |
   | budget        | integer                                |
   +---------------+----------------------------------------+
   

   Where:

   • The id column is automatically set by the database when inserting a new record.
   • The employee_id column references the id column of a record in the employees table.

The physical model

The physical model is the actual implementation of the logical model in a specific database management system, like MySQL.

Its goal is to physically create the tables with concrete system-specific data types, constraints, etc.

For example, the physical model would create:

1. The employees table using this SQL statement:

   CREATE TABLE employees (
     id INT AUTO_INCREMENT PRIMARY KEY,
     first_name VARCHAR(255) NOT NULL,
     last_name VARCHAR(255) NOT NULL,
     department ENUM('Sales', 'Engineering', 'HR', 'Marketing') NOT NULL,
     hire_date DATE NOT NULL,
     salary INT NOT NULL,
     manager_id INT DEFAULT NULL,
     FOREIGN KEY (manager_id) REFERENCES employees(id)
   );
   

2. The contracts table using this SQL statement:

   CREATE TABLE contracts (
     id INT AUTO_INCREMENT PRIMARY KEY,
     contract_name VARCHAR(255) NOT NULL,
     employee_id INT NOT NULL,
     start_date DATE NOT NULL,
     end_date DATE,
     status ENUM('Active', 'Completed') NOT NULL DEFAULT 'Active',
     budget INT,
     FOREIGN KEY (employee_id) REFERENCES employees(id)
   );
   

Column properties

In MySQL, each table column is defined using the following syntax:

column_name column_type [column_attribute ...] [column_constraint ...]

Where:

• column_name is the name of the column.
• column_type is the type of data the column stores.
• column_attribute ... is an optional set of characteristics that define how data is stored in the column.
• column_constraint ... is an optional set of rules that ensure the column adheres to specific conditions or restrictions.

Column data types

MySQL offers various data types for each table column categorized in 3 main groups: numeric, string, date and time.

Choosing the right data type is essential as it optimizes storage, ensures data accuracy, and enhances query performance while enforcing appropriate constraints for the data being stored.

Numeric types

The numeric types include both integers and floating-point numbers:

• Integers

  • BIT(N): Binary data. For example, BIT(8) stores up to 8 bits.
  • TINYINT: An integer from -128 to 127 (Signed), or 0 to 255 (Unsigned).
  • SMALLINT: An integer from -32,768 to 32,767 (Signed), or 0 to 65,535 (Unsigned).
  • MEDIUMINT: An integer from -8,388,608 to 8,388,607 (Signed), or 0 to 16,777,215 (Unsigned).
  • INT: An integer from -2,147,483,648 to 2,147,483,647 (Signed), or 0 to 4,294,967,295 (Unsigned).
  • BIGINT: An integer from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 (Signed), or 0 to 18,446,744,073,709,551,615 (Unsigned).

• Floating-points

  • DECIMAL(M, D): An exact fixed-point precision number where M is the number of digits and D the number of decimals. For example, DECIMAL(5, 2) stores up to 999.99.
  • FLOAT: A floating-point number with single precision.
  • DOUBLE: A floating-point number with double precision.

Note: Attempting to treat FLOAT and DOUBLE as exact in comparisons may lead to problems.

String types

The string types include single characters, variable-size strings, lists of strings, and JSON:

• Characters

  • CHAR(M): A fixed-length string where M is the exact number of characters (padded with spaces if shorter). For example, CHAR(5) stores 5 characters exactly.
  • VARCHAR(M): A variable-length string where M is the maximum number of characters. For example, VARCHAR(255) stores up to 255 characters.

• Large text

  • TINYTEXT: Up to 255 bytes.
  • TEXT: Up to 65,535 bytes (64 KB).
  • MEDIUMTEXT: Up to 16,777,215 bytes (16 MB).
  • LONGTEXT: Up to 4,294,967,295 bytes (4 GB).

• Large objects

  • TINYBLOB: Up to 255 bytes.
  • BLOB: Up to 65,535 bytes.
  • MEDIUMBLOB: Up to 16 MB.
  • LONGBLOB: Up to 4 GB.

• Lists and sets

  • ENUM: A predefined set of unique values. For example, ENUM('small', 'medium', 'large').
  • SET: Similar to ENUM but allows multiple values. For example, SET('read', 'write', 'execute').

• JSON

  • JSON: JSON data in a text format.

Date and time types

The date and time types include dates, times, and timestamps:

• Date

  • DATE: A date in the format YYYY-MM-DD. For example, 2024-06-03.
  • YEAR: A year in the format YYYY. For example, 2024.

• Time

  • TIME: A time in the format HH:MM:SS. For example, 15:32:06.

• Date and time

  • DATETIME: A date and time in the format YYYY-MM-DD HH:MM:SS. For example, 2024-06-03 15:32:06.
  • TIMESTAMP: The current date and time in the format YYYY-MM-DD HH:MM:SS. For example, 2024-06-03 15:32:06.

Column attributes

An attribute is a property or characteristic assigned to a column that defines how data can be stored and managed in that column.

Setting a default value

By default, if the value of a column is unspecified when inserting a new record, its value will be set to NULL.

To set a default value instead, you can use the DEFAULT attribute:

column_name column_type DEFAULT value

For example, if the value of the gas_price column is unspecified, it will automatically default to 1.500:

gas_price DECIMAL(4, 3) DEFAULT 1.500

Incrementing values automatically

To automatically increment the value of a column by 1 when a new record is inserted into a table, you can use the AUTO_INCREMENT attribute:

column_name column_type AUTO_INCREMENT

Note: This attribute can only be used on integer columns (e.g., INT) and if the column is defined as the primary key of the table.

For example, the value of the id column will be incremented by 1 for each new record:

id INT AUTO_INCREMENT

Column constraints

A constraint is a rule (or restriction) applied to a column to enforce data integrity and ensure that the data adheres to specific conditions.

Disallowing NULL values

To prevent the insertion of records with empty columns, you can use the NOT NULL constraint:

column_name column_type NOT NULL;

For example, the NOT NULL constraint forces the value of the email_address column to be specified:

email_address VARCHAR(320) NOT NULL

Enforcing a unique value

To ensure that the value of a column is unique throughout all the records of a table, you can use the UNIQUE constraint:

column_name column_type UNIQUE

For example, the UNIQUE constraint ensures that there will never be two identical values in the email_address column of the table:

email_address VARCHAR(320) UNIQUE

Checking a value

To verify the value of a column before inserting the record into a table, you can use the CHECK constraint:

column_name column_type CHECK condition

For example, the CHECK constraint is used to verify that the value of the delivery column is greater than or equal to the current date:

delivery DATE CHECK delivery >= CURDATE()

Creating new tables

To create a new table, you use the CREATE TABLE statement:

CREATE TABLE table_name (
  column_name column_type [column_attribute ...] [column_constraint ...],
  ...
);

Where:

• table_name is the name of the table.
• column_name is the name of the column.
• column_type is the type of data the column stores.
• [column_attribute ...] is an optional set of characteristics that define how data is stored in the column.
• [column_constraint ...] is an optional set of rules that ensure the column adheres to specific conditions or restrictions.

Example

This SQL statement will create a new table named customers:

mysql> CREATE TABLE customers (id INT NOT NULL, name VARCHAR(100) NOT NULL, email VARCHAR(320) UNIQUE NOT NULL);

Where:

• The id column holds an integer whose value cannot be NULL.
• The name column holds a string with a maximum length of 100 characters whose value cannot be NULL.
• The email column holds a string with a maximum length of 320 characters whose value must be unique and not NULL.

Modifying tables

To modify the structure of an existing table, you can use the ALTER TABLE keywords:

ALTER TABLE table_name ACTION table_name|column_name;

Adding a column

To add a new column to an existing table, you can use the ADD COLUMN keywords:

ALTER TABLE table_name
ADD COLUMN column_name column_type [column_attribute ...] [column_constraint ...]
[FIRST | AFTER column_name];

Where:

• FIRST is optionally used to add the column as the first column of the table.
• AFTER column_name is optionally used to add the column right after the specified column.

Example

This SQL statement will add a new column named email whose length is 255 characters after the column named last_name in the employees table:

mysql> ALTER TABLE employees ADD COLUMN email VARCHAR(255) AFTER last_name;

Modifying a column

To modify the type, attributes, or constraints of an existing column, you can use the MODIFY COLUMN keywords:

ALTER TABLE table_name
MODIFY COLUMN column_name column_type [column_attribute ...] [column_constraint ...];

Example

This SQL statement will modify the email column whose length is 320 characters and must be unique and not null in the employees table:

mysql> ALTER TABLE employees MODIFY COLUMN email VARCHAR(320) UNIQUE NOT NULL;

Renaming a column

To rename an existing column, you can use the RENAME COLUMN keywords:

ALTER TABLE table_name
RENAME COLUMN column_name TO new_column_name;

Example

This SQL statement will rename the email column to email_address in the employees table:

mysql> ALTER TABLE employees RENAME COLUMN email TO email_address;

Dropping a column

To delete (i.e. drop) an existing column, you can use the DROP COLUMN keywords:

ALTER TABLE table_name
DROP COLUMN column_name;

Example

This SQL statement will remove the email_address column in the employees table:

mysql> ALTER TABLE employees DROP COLUMN email_address;

Renaming a table

To rename a table, you can use the RENAME TO keywords:

ALTER TABLE table_name
RENAME TO new_table_name;

Example

This SQL statement will rename the employees table to contractors:

mysql> ALTER TABLE employees RENAME TO contractors;

Dropping a table

To delete (i.e. drop) a table, you can use the DROP TABLE keywords:

DROP TABLE table_name;

Example

This SQL statement will delete the contracts table:

mysql> DROP TABLE contracts;

Summary

Here's a summary of what you've learned in this lesson:

• The conceptual model, which is the highest level of abstraction, focuses on defining the overall structure and content of the database without including specific details like data types or keys.
• The logical model is more detailed than the conceptual model and focuses on the database structure.
• The physical model is the actual implementation of the logical model in a specific database management system like MySQL.
• The DEFAULT attribute is used to set a default value of a column.
• The AUTO_INCREMENT attribute is used to automatically increment by 1 the value of the column for each new record.
• The NOT NULL constraint is used to ensure that the value of a column is not NULL.
• The UNIQUE constraint is used to ensure that the value of a column is unique across all the records of a table.
• The CHECK constraint is used to ensure that the value of a column matches specified requirements.
• The CREATE TABLE keywords are used to create a new table.
• The ALTER TABLE keywords are used to modify the structure of a table.
• The ADD COLUMN keywords are used to add a new column to a table.
• The MODIFY COLUMN keywords are used to modify a column.
• The RENAME COLUMN keywords are used to rename a column.
• The DROP COLUMN keywords are used to delete a column.
• The RENAME TO keywords are used to rename a table.
• The DROP TABLE keywords are used to delete a table.