Database schema
Before developing a database or a software application, you first need to plan how you will organize your data. This plan is referred to as a schema. It’s essentially a blueprint of what your data looks like.
Schema
The general meaning of a schema is that it’s an organization or grouping of information and the relationships among them.
Database schema
In the context of a MySQL database, a schema means a collection of data structures or an abstract design of how data is stored in a database. Essentially, schema and database are interchangeable terms within MySQL. A schema is how data is organized in the database and how it’s related to other data.
But schema is defined in different ways across different database systems.
- In a SQL server, a database schema is a collection of different components like tables, fields, datatypes, and keys.
- In Postgres SQL, a database schema is a namespace with the name database objects like views, indexes, and functions.
- An oracle schema system assigns a single schema to each user. Oracle even names each schema after its respective user.
But no matter which type of database you encounter, the two most important concepts you need to understand when working with schemas remain the same:
- organizational data in the form of tables
- the relationships between the tables
Components of a database
A SQL Server schema is comprised of what are known as schema objects. Many of these objects will probably already be familiar to you from your study of databases.
They include tables, columns, relationships, datatypes, and keys.
An example of a SQL database schema is a music database with data and artists, albums, and genres, all stored in separate tables. However, these tables can still be related to one another through various keys.
In other words, the data within this database is organized in separate tables or entities. You have the tables are also related to one another.
Essentially, a database schema is comprised of all of the important data and the relationships, the unique keys for all entries in databases, and a name and datatype for each column in a table.
Database schema advantages
- Schemas provide logical groupings for database objects.
- They also make it easier to access and manipulate these database objects than other available methods.
- Schemas also provide greater database security. You can grant permission to separate and protect database objects based on user access rights.
- Finally, it’s possible to transfer ownership of schemas and their objects between users and other schemas.
Exploring database schema
In this reading, you’ll explore the concept of a database schema in more detail. You have already been exposed to the concept of a database schema and you have explored a simple example of what a database schema looks like. The main objective of this reading is to explain the concept of database schema in more detail by focusing on the three main types of database schema. Database schema is an important concept to understand in terms of relational databases.
What is a database schema?
Designing the schema or structure of a database is the very first step in designing a database system. Database schema is about the structure of a database. In other words, how data is organized in a database. Data in a database is organized into tables that have columns and rows. Each column or field has a defined data type, and the tables are related to each other. The simplest way of understanding database schema is to think of it as the blueprint of a database. Before anyone can use a database to store and manipulate data, the database schema must first be designed. This process of database schema design is also known as data modeling.
Usually, the database schema is designed by database designers. The database schema is just the skeleton of the database, and it doesn’t store any actual data. Once the designers have provided the database schema, the developers can understand how the data should be stored by the application that they are implementing.
Database schema can be broadly divided into three categories.
- Conceptual or logical schema that defines entities, attributes and relationships.
- Internal or physical schema that defines how data is stored in a secondary storage. In other words, the actual storage of data and access paths.
- External or view schema that defines different user views.
Conceptual or logical schema
The conceptual or logical schema describes the structure of the entire database for all the users. It describes the structure in terms of entities and features of the entities and the relationships between them. An Entity Relationship Diagram (ER-D) is usually drawn to represent the logical schema of a database. At this level, details about the physical storage and retrieval of data are hidden, and the database structure is described only at a concept level. The software developers work with the database at this level.
Here’s an example of a logical schema.
This depicts the employee and department entities in the database along with their attributes and how these two entities are related to each other. This is just a simple example and there’ll be more entities in a real database.
Internal or physical schema
The internal or physical schema describes the physical storage of the database. It represents the entire database but at a very low level. This means it describes how the data is really stored on disk in the form of tables, columns and records. It defines what data is stored in the database and how.
Here’s an example of what’s meant by an internal schema.
This example depicts how the employee table should physically store its data. A real database would have more tables and the internal schema would describe the physical representation of all those tables in the entire database.
External or view schema
The external or view schema describes the database like an external user would want to see it. This schema only describes the part of the database that the specific user is interested in. It hides the nonrelevant details of the database from a user. For example, a user from the sales department will see only sales-related data in a database. There can be many external schemas of a single database for different users. Examine the following example of view schema.
There are three different schemas of how three different users want to view the employee table. Though this example only includes the employee table, in the actual database there’ll be many tables, namely employee, department, job, invoice and so on. The different view schemas will have different subsets of the database.
Also, these different view schemas will have different subsets of the attributes in the tables. For example, User1’s view schemas could have employee, department and job tables. User2’s view schema could have employee, department and invoice tables. And User3’s view schemas could have all four tables. The attributes of tables that are available to these three different user view schemas could be different as well.
These three levels of schema are also known as the three-schema architecture. It can be diagrammatically depicted like this.
Why are database schemas important?
A database schema helps database engineers to organize data into well-defined tables with relevant attributes in them. It also shows the interrelationships between tables and depicts the data types that each column must have. A well-designed database schema makes life easier for database engineers as well as developers. It helps to:
- Maintain a clean set of data in the database related to an application.
- Avoid reverse-engineering of the underlying data model from time to time.
- Write efficient queries to retrieve data for reporting purposes, analytics and so on.
In other words, it prevents you from ending up with a database design that requires a database engineer to do a lot of reverse-engineering down the line, wasting time and effort that leads to increased costs for organizations.
Schema in use
Shopping cart database, with three tables:
CREATE DATABASE shopping_cart_dbCREATE TABLE customer(
customer_id INT,
name VARCHAR(100),
address VARCHAR(255),
email VARCHAR(100),
phone VARCHAR(10),
PRIMARY KEY (customer_id)
);CREATE TABLE product(
product_id INT,
name VARCHAR(100),
price NUMERIC(8,2),
description VARCHAR(255),
PRIMARY KEY (product_id)
);CREATE TABLE cart_order (
order_id INT,
customer_id INT,
product_id INT,
quantity INT,
order_date DATE,
status VARCHAR(100),
PRIMARY KEY (order_id),
FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
FOREIGN KEY (product_id) REFERENCES product(product_id)
);Types of database schema
When creating your databases, you need to be able to distinguish between different kinds of database schemas. In other words, you need to answer the question of what kind of database best suits my project?
Logical database schema
A logical database schema is how the data is organized in terms of tables. In other words, it shows what tables should be in a database, and explains how the attributes of different tables are linked together.
Creating a logical database schema means illustrating relationships between components of your data. This is also called entity relationship or ER modeling. It specifies what the relationships between entity types are.
It demonstrates the relationship between an order, the shipment in which it will be shipped, and the courier assigned to it.
The ID attribute in each table is the primary key of the respective entities. It provides a unique identifier for each entry, row, or record in the entities.
In the order entity, the shipment ID and courier ID are called foreign keys. But in fact, they are also the primary keys of the shipment and courier entities respectively. This creates a relation between these entities and the order table, which in turn has its own ID as its primary key.
Physical schema
Physical schema is how data is stored in disk. In other words, this involves creating the actual structure of your database using code.
In my SQL, another relational databases, developers use SQL to create the database tables and the other database objects.
However, physical schema creation could differ slightly between different database systems.
Database schemas are vital when it comes to the creation of databases and they form the basis of your application.
You should also be able to describe how a logical database schema refers to the organization of data in tables, and that you’re using an ER model to specify relationships between entities. You should also now note that you can control how data is physically stored on disk by creating a physical schema with SQL statements.
Building a schema
In this reading, you’ll be taken through an example of building a simple database schema. You’ve learned about the concept of a database schema and what needs to be done to build it. The main objective of this reading is to present a more complex example of building a database schema.
It is highly recommend that you follow along and write the code in a MySQL environment on your own machine so you can see the schema you are building.
Database schema
Building a database schema is the first step in database design. It is essential especially when you are dealing with relational databases because you want a solid structure for your database before you can move forward. A database schema is like a blueprint of how data in a database will look and be stored.
A schema consists of what’s known as schema objects. Schema objects could be things like tables, columns and relationships, at a minimum. Data types, views, stored procedures, primary keys and foreign keys are also schema objects.
Basically, a database schema consists of:
- all the important data pertaining to a given scenario and their relationships,
- unique keys for all entries and database objects,
- and a name and data type for each column in a table.
Building a database schema for a restaurant booking scenario
When building a database schema for a restaurant booking system there are a few things you must consider. You must consider that customers make reservations for tables and those tables have orders associated with them. An order will have associated menu items that belong to a menu. And the orders are served by a waiter.
The logical database schema
Now let’s examine how to build a logical database schema for this scenario. In an example like this, database engineers usually draw a diagram known as ER-D (Entity Relationship Diagram).
The logical database schema consists of entities that become tables in the physical database design. Each entity has a set of attributes and one of them (sometimes even two) makes each entity instance, or row of data, unique. These attributes are known as the primary key. These primary key attributes are also present in other tables that the table relates to. In the related table, this key is known as the foreign key.
This is the logical schema or the ER-D for the scenario.
The physical database schema
Now let’s build the physical database schema for this scenario based on the logical database schema that was designed in the previous section. The first step is to create the restaurant database.
To create the restaurant database, the CREATE DATABASE SQL syntax is as follows:
- CREATE DATABASE restaurant;
The next step is to create the tables inside this database. The tables and their fields or columns that need to be created are found in the logical database schema. Appropriate data types must be used when defining table columns. This enables memory allocation to take place correctly during physical data storage.
So, let’s examine the syntax for creating each table.
The first table ‘tbl’ represents a tablein the restaurant. It has a unique ID and a location – where it’s placed in the restaurant. The unique ID is the primary key of this table.
CREATE TABLE tbl(
table_id INT,
location VARCHAR(255),
PRIMARY KEY (table_id)
); This next table contains data about waiters who work in the restaurant. They have a unique ID, a name, their contact number and which shift they usually work. The primary key of the table is the unique ID assigned to the waiter.
CREATE TABLE waiter(
waiter_id INT,
name VARCHAR(150),
contact_no VARCHAR(10),
shift VARCHAR(10),
PRIMARY KEY (waiter_id)
); The following syntax creates the table that stores data about orders for each table. It has the order ID and table ID fields. As well as a date_time field to capture the date and time of the order and the ID of the waiter who’s supposed to serve that table, for that order.
CREATE TABLE table_order(
order_id INT,
date_time DATETIME,
table_id INT,
waiter_id INT,
PRIMARY KEY (order_id),
FOREIGN KEY (table_id) REFERENCES tbl(table_id),
FOREIGN KEY (waiter_id) REFERENCES waiter(waiter_id)
); This table stores data about customers. It has a customer ID, name, NIC number to store the National Identity Card number and the contact number fields. The primary key is the unique customer ID field.
CREATE TABLE customer(
customer_id INT,
name VARCHAR(100),
NIC_no VARCHAR(12),
contact_no VARCHAR(10),
PRIMARY KEY (customer_id)
); The reservation table associates an order with a customer. It has a unique ID, a date and time, number of guests or pax expected, the order_id, table_id and the customer_id. Its primary key is the unique reservation_id. This table is linked with the tbl, table_order and customer tables.
CREATE TABLE reservation(
reservation_id INT,
date_time DATETIME,
no_of_pax INT,
order_id INT,
table_id INT,
customer_id INT,
PRIMARY KEY (reservation_id),
FOREIGN KEY (order_id) REFERENCES table_order(table_id),
FOREIGN KEY (table_id) REFERENCES tbl(table_id),
FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
); This menu table stores all the menus of the restaurant. It has a menu_id which is the unique field that contains descriptions of the menu and its availability.
CREATE TABLE menu(
menu_id INT,
description VARCHAR(255),
availability INT,
PRIMARY KEY (menu_id)
); Every menu can have unique menu items and these menu items are stored against the menu, in the menu_item table. A menu items also has description, price and availability fields. This table links with the menu table.
CREATE TABLE menu_item(
menu_item_id INT,
description VARCHAR(255),
price FLOAT,
availability INT,
menu_id INT,
PRIMARY KEY (menu_item_id),
FOREIGN KEY (menu_id) REFERENCES menu(menu_id)
); This final table captures the menu items ordered for a specific order. It has the order_id, menu_item_id and the quantity ordered. It has a composite primary key of order_id and menu_item_id field combination and its linked with the table_order and menu_item tables.
CREATE TABLE order_menu_item(
order_id INT,
menu_item_id INT,
quantity INT,
PRIMARY KEY (order_id,menu_item_id),
FOREIGN KEY (order_id) REFERENCES table_order(order_id),
FOREIGN KEY (menu_item_id) REFERENCES menu_item(menu_item_id)
); These CREATE TABLE statements create all the tables within the reservation database. The important thing to note is how the relationships are established between the tables. Each table is defined with a primary key, and that in turn becomes the foreign key in the related table.
In conclusion, this is how a basic database structure or schema can be created using SQL DDL (Data Definition Language) syntaxes.
Additional resources
The following resources cover database schemas, demonstrating the different types, and clarifying how database schema can create definition and organization of data within a relational database.
Previous one → 8.Sorting and filtering data | Next one → 10.Relational database design