a shot of dev knowledge


What is a database schema?

A database schema is an abstraction used to represent the storage of data in a database. It not only describes the organization of data but also represents the relationship between various tables in a database.


There are two types of database schemas.

Logical Database Schema

This represents how the data is organized in terms of tables, and how the different attributes in the different tables link them. Different database management systems use different syntax to define the logical architecture and constraints of a database which is called logical database schema.

Physical Database Schema

This represents how data is stored physically on disk storage.

The difference between these two schemas could be seen in the table below.

svg viewer
Difference between logical and physical Database Schema

Database schema is Just a representation and it does not contain any data.

Two important types of keys are:

Primary key: It is used to uniquely identify a record in the table.

Foreign key It is an attribute that is used as a primary key in another table.

svg viewer
Database schema Illustration

The following cope snippet defines the schema of a table(mapdata) in MySQL.

       CREATE TABLE mapdata (
       id integer NOT NULL AUTO_INCREMENT,
       mapid integer NOT NULL,
       x integer NOT NULL,
       y integer NOT NULL,
       z integer NOT NULL,
       value varchar(1),
       UNIQUE KEY(id))



View all Courses

Keep Exploring