SQL Database Structure

In this article, I’ll be discussing the building blocks that make up databases and give them their important attributes. To do so, we will be working further with our example database “test_database”, which we created in this previous article.

General Structure

Databases are potentially enormous collections of data, intended to be made easily available to both applications and individuals. In order to be effective, databases must be efficiently organized. Databases can all be broken into three key organizational components:

Tables
Columns
Rows

Tables

Tables are where the majority of our data will be stored in the database. Every database will likely have multiple tables, with each one being used to store similar objects and information. These are typically what we’re accessing when we query a database for data.

For our example database, we’ll be creating a table called “cars”, which will store all of the information that we have on – you guessed it – cars.

Columns

Columns are entities that exist within tables. Every column within a table represents a specific attribute of the greater table. For example, if our table is called “cars”, then one of our columns might be called “model”, to keep track of the model of every car that we store within the table. Every column will also have a data type, which can take a multitude of shapes, like integer and text.

In our example, we’ll be storing the make, model, and year of each car, so we’ll need to create columns for these.

Rows

Finally, every table will also have multiple rows. Where the columns of a table represent different fields or features of each object in the table, every row in the table represents an object. In our case, every row within the “cars” table will represent an individual car.

Creating the Structure

In order to add the above mentioned structures to our test database, we’ll need to make use of a few new SQL statements. It’s important to note that SQL commands can be broken down into 5 different types, and this article will focus on one of those types: DDL (Data Definition Language), which is used to define or modify the structure of a database.

First, let’s reconnect to the database we created by switching to the postgres user and running the psql command:

Bash

sudo -u postgres -i
psql

Next, we’ll run the postgres command to connect to our database:

SQL

\c test_database

Now that we’re reconnected to the database, we’ll need to make use of the CREATE TABLE statement. We’ll need to provide the desired table name, along with what columns and datatypes this table will have.

SQL

CREATE TABLE cars (
    id    SERIAL PRIMARY KEY,
    make  TEXT,
    model TEXT
);

In the above SQL statement, we’re creating a table that includes “make” and “model” as text fields.

Notably, we’re also defining an “id” column of type SERIAL. This type represents an automatically incrementing integer, meaning that every new entry we add to this table will be given a unique ID (starting with 1). This column is also labeled PRIMARY KEY, which simply means that this column can be used to uniquely identify each row.

It’s important to note that a table can only have one primary key, and it’s good practice to have a primary key column within every table. This allows every object in the database to be referenced by a unique ID.

If we had a table already in existence that we wanted to update, we would use the ALTER TABLE statement to do so. Since we forgot to add the “year” column that was mentioned earlier, we can add the column now like so:

SQL

ALTER TABLE cars
ADD COLUMN year INTEGER;

DROP Statements

Before moving on, it’s important to be aware of the commands used to remove aspects of our database structure. As an example, if you wanted to remove one of the columns we’ve defined on our “cars” table, the ALTER TABLE statement would still need to be used to adjust the table, instead “dropping” one of the columns:

SQL

ALTER TABLE cars
DROP COLUMN year;

If your intention was to get rid of the “cars” table altogether, the DROP TABLE statement would instead be used:

SQL

DROP TABLE cars;

Using the SQL statements discussed above, a wide range of database structures can be created and adjusted in order to fit many unique storage situations. However, to make full use of our database, we’ll need to actually fill it with data – and interact with that data. Check out this article where I’ll begin to discuss INSERT, SELECT, UPDATE, and DELETE statements to allow us to do exactly that.

MavigBot