4 Database Schema Examples for Various Applications

•

November 26, 2024

•

20 Mins Read

Whether you create a web application, e-commerce platform, or customized system for your organization, designing the right database schema is the key to effective data handling. You have to think through and decide what datasets you need and how you will use them to streamline your workflows.

Depending on the type of application you develop, your approach to database schema also changes. Through this article, you will thoroughly explore the concept of database schema and its types. It will also discuss four database schema examples tailored to different use cases and provide sample code for each of them.

What Is Database Schema?

Database schemas are the blueprints of your database. They help you define the structure and relationships between various data elements. A well-planned schema ensures your data’s integrity, enables efficient querying, and increases scalability. It provides a better understanding of how data is organized and what rules govern the data flow.

With database schemas, you can map tables, dependencies, and data types and effectively communicate your application’s data architecture to various stakeholders. This further helps you get feedback from different teams involved in the project and discuss any shortcomings early on. Structuring your data also helps identify data redundancy and inconsistencies, reducing future storage and computational resource requirements.

Database Schema Types

You can broadly categorize database schemas into three types: Conceptual, Logical, and Physical.

Conceptual Schema: It provides a high-level overview of your database system and is developed during a project’s initial stages. You can use conceptual schema to outline the system's requirements and incorporate necessary business rules. ‍
Logical Schema: This schema defines the structure of the database by specifying entity relationships, table names, column names, constraints, and other schema objects. It translates the conceptual schema into a more technical representation.
‍Physical Schema: It provides all the technical information needed to design your database system, such as syntax or actual code. Physical schema describes the physical storage and organization of data, including file structures and indices.

These schema types represent different levels of abstraction and provide a framework to develop your database. To implement this framework effectively, you can use several schema designs based on data structures and query patterns. Some common examples of database schema designs include:

Flat Schema

A flat schema is a simple, two-dimensional array where data is organized in rows and columns. Each column has the same type of data, and all elements in a row are related to one another. This schema design follows the “Keep It Simple, Stupid!” (KISS) principle and is best for applications with no complex data.

Star Schema

A star schema has a central fact table connected directly to several dimension tables. The fact table stores the primary key of a dimension table as a foreign key. It contains numerical data related to business operations, such as the number of products sold.

On the other hand, dimension tables store descriptive information about the fact table (for example, the promotion type used to sell those products). Star schema is ideal for structuring the data of relational database management systems (RDBMS).

Snowflake Schema

A snowflake schema is derived from a star schema. It also has a central fact table and multiple dimension tables. However, the main difference between a star vs snowflake schema is that the dimension tables are further normalized into sub-dimensional tables in a snowflake schema. This schema improves data integrity and is used to support advanced data analytics.

Galaxy Schema

Galaxy schema involves multiple fact tables connected by shared, normalized dimension tables. This structure achieves near-zero data redundancy due to normalization, ensuring high data quality and accuracy, and enables efficient reporting and analytics. Galaxy schema is suitable for complex database systems requiring a multidimensional approach.

One-to-Many Schema

A one-to-many schema represents a relationship where a single record in one table (parent) is associated with multiple records in another (child). It is also called a foreign key constraint that helps you achieve data deduplication. One-to-many schema is generally used in relational databases to represent hierarchical relationships.

Network Schema

A network schema can be used when various potential paths lead to the same outcome. In this schema, a record in one table can be linked to multiple records in other tables and vice versa. It allows you to implement many-to-many complex database relationships similar to what is seen in real-world data.

4 Database Schema Examples for Various Applications

Database schemas play a crucial role in efficiently storing and retrieving data. They help you understand how data flows through your system to implement a specific task. Here are four popular examples of schema in databases for various use cases across industries.

E-Commerce Schema

A schema for an e-commerce platform begins by identifying the various entities involved in this application, such as customers, products, orders, payment, and inventory. After this, you can list the data elements each table will store and which of these will be primary and foreign keys.

For example, let’s consider Customers, Products, Orders, and Order_Items as entities for our schema. Here is the breakdown of what each of these entities will contain:

Customers: This table stores information about registered users, including customer_ID, name, email, password, address, and other required information. Create this table by referring to the code below.


CREATE TABLE Customers (
    customer_id SERIAL PRIMARY KEY,
    cust_name VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    shipping_address TEXT NOT NULL,
);

Products: The product table contains product information, such as product ID, name, description, price, and dimensions. Here is the sample code to create this table.


CREATE TABLE Products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    product_description TEXT,
    product_rate DECIMAL(10,2),
);

Orders: This table stores all the details about orders placed by the customers and has data elements such as order_ID, customer_ID, order_status, delivery_date, and more. Here, customer_ID is declared a foreign key to reference the Customers table.


CREATE TABLE Orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    order _status VARCHAR(25) NOT NULL,
    delivery_date DATE NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

Order_Items: The Order_Items table stores individual items within an order. It references the Products table and has values like total amount, shipping_date, and more.


CREATE TABLE Order_Items (
    orderitem_id SERIAL PRIMARY KEY,
    product_id INT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    shipping_date DATE NOT NULL,
    FOREIGN KEY (prouduct_id) REFERENCES Products(prouduct_id)
);

Based on the above explanations, your database model diagram will resemble the representation below.

Healthcare System Schema

Using this database schema example for a healthcare system, you can easily maintain patient records and schedule doctor appointments. The typical entities for the schema design include patient, appointment, and doctor.

#1. Patient: This table contains all the information on a patient, such as their name, address, diagnosis, and more. Its attributes include:

patient_id: Primary key to identify unique patients.
patient_name: Name of the patient.
patient_address: Stores the patient's residential address.
age: Age of the patient.
gender: Records the gender of the patient.
diagnosis: Contains information about the patient’s diagnosis.

#2. Appointment: The appointment table helps you manage patient appointments by connecting patients to doctors and tracking their status. It can have data elements like:

appointment_id: Uniquely identifies each appointment.
appointment_date: Records the appointment date.
patient_id: Foreign key referencing the Patient table.
doctor_id: Foreign key referencing the Doctor table
appointment_status: Tells if the appointment is scheduled or canceled.

#3. Doctor: This entity includes information about the doctor, like their name, specialization, and availability. Its attributes include:

doctor_id: Primary key for the Doctor table.
Doctor_name: Stores the doctor’s name.
specialization: Mentions the doctor’s specialization.
availability: Tells if the doctor is available, booked, or off-duty.

You can utilize the sample code below and create a data system for this database schema example.


CREATE TABLE Patient (
    patient_id SERIAL PRIMARY KEY,
    patient_name TEXT NOT NULL,
    patient_address TEXT,
    age INT CHECK (age > 0),
    gender ENUM(‘Male’, ‘Female’, ‘Other’) NOT NULL,
    diagnosis TEXT 
);

CREATE TABLE Doctor (
    Doctor_id SERIAL PRIMARY KEY,
    doctor_name VARCHAR(100) NOT NULL,
    specialization TEXT NOT NULL,
    availability ENUM(‘Available’, ‘Booked’, ‘Off-duty’) NOT NULL
);

CREATE TABLE Appointment (
    appointment_id INT PRIMARY KEY AUTO_INCREMENT,
    appointment_date DATE NOT NULL,
    patient_id INT NOT NULL,
    doctor_id INT NOT NULL,
    appointment_status ENUM(‘Scheduled’, Canceled’) DEFAULT ‘Scheduled’,
    FOREIGN KEY (patient_id) REFERENCES Patient(patient_id),
    FOREIGN KEY (doctor_id) REFERENCES Doctor(doctor_id)
);

The example of schema in database for a healthcare system will resemble the ER diagram shown below. Based on your requirements, you can add other tables like Billing, Departments, Pharmacy, and more.

CMS Schema

A Content Management System (CMS) is used to publish, edit, delete, and organize content (articles, blogs, web pages) from a single interface. The functionalities of a CMS include defining user roles and permissions, identifying content types, and communicating ideas, feedback, or approvals.

Once requirements are clear, you can identify the relevant entities (tables) and how they relate. To simplify the explanation, let’s consider one utility of CMS: categorizing content for better organization. Common entities for this CMS database function can include:

#1. Users: To store information about CMS users such as authors, editors, and administrators who manage content. It can have fields like:

user_id: Primary key identifying unique users.
user_name: Name of the user.
email: User’s email address.
password: User’s password to log in.
role_id: Foreign key referencing the Roles table.

#2. Roles: To define different roles and their associated permissions. You can use the attributes below:

role_id: Primary key, a unique identifier for each user’s role.
role_name: Provides the title for CMS users, such as author or editor.

#3. Digital_Content: Represents the actual content items, such as articles or pages, and can have fields such as:

content_id: Uniquely identifies the content created.
media_type: URL that stores the associated media files.
meta_desc: Provides a short description of the content.
created_date: Time of content creation.
content_status: Explains if the content is being drafted, edited, reviewed, or published.

#4. Categories: Used for organizing and classifying content based on pre-defined categories. It can data elements like:

category_id: Unique identifies for each category listed.
category_name: Category to which the content belongs.

Here is the sample code for you to get started:


CREATE TABLE Users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    user_name VARCHAR(100) NOT NULL,
    email TEXT NOT NULL UNIQUE,
    password VARCHAR(50) NOT NULL,
    role_id INT NOT NULL,
    FOREIGN KEY (role_id) REFERENCES Roles(role_id)
);	

CREATE TABLE Roles (
    role_id SERIAL PRIMARY KEY,
    role_name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE Categories (
    category_id SERIAL PRIMARY KEY,
    category_name TEXT NOT NULL UNIQUE
);

CREATE TABLE Digital_Content (
    content_id INT PRIMARY KEY AUTO_INCREMENT,
    media_type VARCHAR(50) NOT NULL,
    meta_desc TEXT NOT NULL,
    created_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    content_status ENUM(‘draft’, ‘editing’, ‘review’, ‘published’) DEFAULT ‘draft’,
    category_id INT NOT NULL,
    user_id INT NOT NULL,
    FOREIGN KEY (category_id) REFERENCES Categories(category_id),
    FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

Here is the ER diagram for this database schema example:

Social Media Platform Schema

A social media platform database is designed to manage user interactions, connections, and content sharing effectively. An example of schema in database for a social media platform can include tables for users, posts, comments, and followers.

For a simplified explanation, let’s consider only three entities—Users, Posts, and Liked_Posts. Here is a detailed overview of all the attributes that can be added to each table:

#1. Users Table: This table stores profile information and login credentials of the users registered on the social media platform. Below are some of the data elements you can add:

user_id: Primary key for the Users table.
user_name: User's display name.
email: User’s email address.
password: Password for login
bio: Short description of the user.
profile_picture: URL to the user's profile picture.

#2. Posts Table: The Posts table contains details about the content shared by the user. This table can include attributes such as:

post_id: Primary key for each post.
user_id: Foreign key that references the Users table.
post_content: URL of the media posted.
Media_type: Mentions the type of media shared (image, video, or audio).
post_desc: Textual content of the post.
post_created_at: Records the time of the post creation.
likes: Keeps count of the likes on a post.

#3. Liked_Posts Table: This table helps you track interactions on each post and has two foreign keys to link the Users table and Posts table. Its attributes include:

post_id: Foreign key referencing the Posts table.
user_id: Foreign key referencing the Users table.

You can utilize the following sample code to help you create this database schema example:


CREATE TABLE Users(
    user_id SERIAL PRIMARY KEY,
    user_name VARCHAR(100) NOT NULL,
    email TEXT NOT NULL,
    password TEXT NOT NULL,
    bio TEXT NOT NULL,
    profile_picture TEXT NOT NULL
);

CREATE TABLE Posts(
    post_id SERIAL PRIMARY KEY,
    media_type VARCHAR(20) NOT NULL,
    post_content TEXT NOT NULL,
    post_desc TEXT NOT NULL,
    likes INT NOT NULL,
    post_created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    user_id INT NOT NULL REFERENCES Users(user_id) ON DELETE CASCADE
);

CREATE TABLE Liked_Posts(
  post_created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  post_id INT NOT NULL REFERENCES Posts(post_id) ON DELETE CASCADE,
  user_id INT NOT NULL REFERENCES Users(user_id) ON DELETE CASCADE
);

For this example, database schema diagram will look like this:

Streamlining Data Integration with Airbyte

Managing data that is spread across multiple locations and has varying database schemas can be a complex task. This is where Airbyte, an AI-enabled data integration platform, can help you. It simplifies data management by allowing you to build scalable data pipelines to consolidate fragmented data into a unified, centralized repository.

Some key features of Airbyte include:

Streamlined Data Ingestion: Airbyte allows you to collect data from disparate sources using a library of over 400 pre-built connectors. You can also build custom connectors from scratch using a low-code Connector Development Kit (CDK), Java-based CDK, or no-code Connector Builder. To further speed up the pipeline development, you can rely on the AI assistant available in the Connector Builder feature. It scans through the API documentation, pre-fills several fields, and even offers intelligent suggestions to fine-tune your configurations. ‍
GenAI Workflows: With Airbyte, you can simplify your GenAI workflows by loading semi-structured and unstructured data directly into vector store destinations. The platform supports several vector databases such as Chroma, Milvus, Pinecone, Qdrant, and Weaviate, offering plenty of resources to execute your AI applications with ease. ‍
Transformation: You can integrate Airbyte with dbt Cloud to perform custom transformations and with LLM frameworks like LangChain or LlamaIndex to perform RAG technique transformation. Airbyte enables you to implement automatic chunking, indexing, and embedding to transform your raw data into a format suitable for vector databases.

Airbyte has also announced the general availability of the Self-Managed Enterprise edition to support your evolving data needs. For more information, you can refer to the official documentation.

Final Thoughts

Database schemas are the most fundamental aspects of building a data-driven application. By understanding the different types of schemas and their benefits, you can decide which suits your data system the best. This article discusses four schemas in database examples to help you get started with the basics of creating schemas.

While designing schemas, the most important thing is to analyze what functionalities you want your application to implement and identify the required tables. After this, you should consider the relationship between data elements across tables. By thoroughly thinking about the objectives you want to achieve, you can build reliable database schemas and manage your evolving data requirements.

Suggested Reads:

Database Schema Migration

Limitless data movement with free Alpha and Beta connectors

Introducing: our Free Connector Program

The data movement infrastructure for the modern data teams.

Try a 14-day free trial