What is a Database Schema?

There are three steps to designing database schemas commonly used in a database management system.

1. Conceptual database schema

A conceptual database schema design is the highest-level view of a database, providing an overall view of the database without the minor details. A conceptual database schema design is typically a quick drawing by hand.

For example, relational databases store data in tables, with each table containing a set of related data. A conceptual database schema might describe a product table and its attributes, a customer table, and a many-to-many relationship between tables. However, a conceptual database schema may not contain finer implementation details, such as data types or access constraints.

A conceptual schema is useful for charting the overall data flow in an organization, without offering too much detail.

2. Logical database schema

A logical database schema design provides an outline of how data within a database is structured. It describes the relationship between entities and shows more details about how data is organized. A logical database schema design is typically a digital data modelling exercise.

Each entity in the data schema is defined in relation to information such as:

• Table names
• Entity relationships
• Attribute names
• Default values
• Data types
• Security constraints
• Procedures
• Views
• Indexes
• Metadata

A complete logical schema design ensures data consistency and integrity by providing constraints for new and existing data.

Logical database schemas do not typically include any technical requirements.

3. Physical database schema

A physical database schema describes exactly where data can be found within a broader database structure. It includes technical storage details, identifying the file locations, specific storage formats, and indexing strategies used by each table to store its data. A physical schema design is typically a combination of fixed database technical design patterns and user specifications.

The physical schema is the least conceptual form of database schema and offers real insight into data locations. Logical and physical schemas are required for database instatiation.

The process of designing a database schema is called data modeling.

Here are the main steps to produce a data model of an OLTP system.

Gather requirements

Before creating a database, you must identify its purpose and outline the key information, such as the data you want it to contain and how you plan to use the database. The best database for you will vary depending on:

• Specific data you use
• Queries you need to interact with the database
• Reports you want to generate

This step outlines your objectives, guiding your database schema design process.

Create entity-relationship diagrams

An entity relationship diagram (ERD) maps out how tables, database objects, and individual entities within a database connect. Creating a conceptual schema view of your database enables you to visualize how the database functions and gain insight into the data it stores.

At this stage, you can also define the naming conventions that tables, columns, database objects, and indexes use in your database. Conventions help everyone to have a standard approach when inputting data.

Organize data entities into tables

Based on your ERD map, you can now organize all of your data into specific tables. Each entity in your database structure should have its own table, with individual columns holding related attributes. Define the primary key that will allow you to easily identify and retrieve specific data values.

Normalize data structures

Normalization is a process in database schema design aimed at reducing data redundancy and improving data integrity. It involves organizing data into tables in such a way that relationships among the data are well-structured and anomalies are minimized.

There are several normal forms, each with specific requirements. Each successive normal form addresses a different redundancy or dependency type to enhance data consistency and make the schema more robust.

1NF

1NF requires that each column contains atomic (indivisible) values and that each record is unique. It removes repeating groups and multi-valued fields.

2NF

2NF builds on 1NF by ensuring that all non-key attributes are fully functionally dependent on the entire primary key (i.e., it eliminates partial dependencies).

3NF

3NF adds that all non-key attributes must depend only on the primary key, and not on other non-key attributes (i.e., it removes transitive dependencies).

Implement security measures

Create a permissions structure to ensure that only authorized users can access your database and view the information it contains. You can assign distinct privileges to different user groups in the database, such as the ability to read, write, or delete information, which helps keep your sensitive data safe. Define role based access controls to ensure that only authorized users can view or modify sensitive data.

Test

Testing your database schema design with some basic queries and other interactions ensures everything functions as intended. Collecting data on how the database functions at this stage will inform any additional changes you need to make to ensure your schema is effective and free from performance issues.

A database schema conversion is the process of adapting an existing database schema to a new format. This may involve adding or modifying tables, columns, indexes, constraints, or relationships between tables.

The goal is often to support new application requirements, improve performance, or move to a different database system. Schema conversion enables more efficient data organization or to support features of a new system.

Data migration may or may not require schema conversion, depending on the source and destination databases.