How to Write Proper, Well Structured mySQL Database Schemas
When developing a software project one of the most important, foundational and intrinsic aspects is a properly structured database schema. It's the equivalent of when building a house you need to ensure the foundation is properly laid, otherwise the chances of building a quality house are drastically reduced. Surprisingly easier than one would think, let's learn the various facets used to write a well architect ed database schema.
CREATE TABLE Syntax
To begin, open your favourite text editor to a new file as a database schema is nothing more than a plain text file. A database consists of multiple tables each of which consist of columns, and the CREATE TABLE syntax is used to create a single table. Here's a basic example:
CREATE TABLE users (
id INT NOT NULL,
is_active TINY INT NOT NULL,
full_name VAR CHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL
);
As you can see this will create a database table named
Use Correct Column Types
As shown above, the columns the table will consist of are separated by commas. Each column definition is comprised of the three same parts:
<cb:COL_NAME TYPE [OPTIONS]
The name of the column, followed by the column type, then any optional parameters. We'll get into the optional parameters later, but concentrating on the column type, below lists the most commonly used column types available:
For all intents, the above column types are all that you need to write well constructed mySQL database schemas.
Define Column Options
When defining columns there are also various options you may specify. Below is another example of the
CREATE TABLE users (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(100) NOT NULL UNIQUE,
status ENUM('active','inactive') NOT NULL DEFAULT 'active',
balance DECIMAL(8,2) NOT NULL DEFAULT 0,
date_of_birth DATETIME,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
The above may look a little daunting, but fret not, it's quite simple. Broken down, here's what is happening in the above statement:
- You should always use NOT NULL on all columns possible to help with speed and performance of the table. This simply specifies the column can not be left empty / null when a row is inserted.
- Always try to keep the column size as small as realistically possible, as it helps improve speed and performance.
- The
- The
- The
- The
- Last, the
The above is an example of a nicely structured database table, and should be used as an example going forward.
Link Tables Together With Foreign Key Constraints
One of the greatest advantages of using relational databases such as mySQL is its excellent support for foreign key constraints and cascading. This is when you link two tables together by a column, forming a parent child relationship, so when the parent row is deleted the necessary child rows are also automatically deleted. Here's an example:
CREATE TABLE users (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(100) NOT NULL UNIQUE,
full_name VARCHAR(100) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) engine=InnoDB;
CREATE TABLE orders (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
userid INT NOT NULL,
amount DECIMAL(8,2) NOT NULL,
product_name VARCHAR(200) NOT NULL,
FOREIGN KEY (userid) REFERENCES users (id) ON DELETE CASCADE
) engine=InnoDB;
You will notice the FOREIGN KEY claue s the last line. This line simply states this table contains child rows that are linked by the
Also note the <c:engine=InnoDB> at the end of the above statement. Although InnoDB is now the default mySQL table type, it wasn't always, so this should be added just to stay on the safe side. Cascading only works with InnoDB tables, and not the other popular table type of myIsam.
Design With Confidence
You're now well on your way to architecting solid, well structred mySQL database schemas. Using the above knowledge you can now write well organized schemas that provide both, performance and structural integrity.