5 Database Schema Design Example: Critical Practices & Designs

Databases are the cornerstone of almost all business projects. As a result, organizations should focus on designing superior databases to meet the objectives of projects without losing direction. Failing to do so may cost time, money, and can put the whole project in jeopardy. Consequently, Database Schema Design Examples and the Database Schema Example have gained prominence over the years that help users understand Databases easily.

Enterprises employ a myriad of dedicated systems for application-specific use-cases. For instance, RDBMSs are used for transactional data, data lakes for raw data workloads, and data warehouses for batch and near-real-time analytics.

With scale, these specifics become challenging for the end-user as combining the different sources of data requires mapping each source into a schema. But with a well-devised Database Schema design, organizations can have a foolproof plan to maintain their data pipelines and meet their business objectives.

This blog talks about the Database Schemas and their types eliciting the 5 key Database Design Schema Example. It wraps up with the key practices to follow for optimal performance.

Table of Contents

• What is a Database Schema?
• Importance of Database Schema Design
• How to Design a Database Schema?
• Best Practices for Database Schema Design
• Understanding the Data Model
• What is Data Normalization?
• What are Entity-Relationship Diagrams?
• Types of Database Schemas
  • Physical Database Schema
  • Logical Database Schema
  • View Database Schema
  • Star Schema
  • Snowflake Schema
• Database Design Schema Examples
  • Schema Example: E-Commerce Transaction
  • Schema Example: Online Banking
  • Schema Example: Hotel Reservation
  • Schema Example: Restaurant Booking
  • Schema Example: Financial Transaction
• Designing Schema Example: Key Practices
  • Have Good Naming Standards
  • Use Normalization to Tackle Redundancy
  • Fix the Right Number of Tables
  • Avoid Nulls
  • Have Proper Documentation
  • Protect Data Integrity
  • Use Stored Procedures to Access Data
• Creating Relationships between Entities
• Multi-dimensional Data
• Data Integrity Rules
• Conclusion

What is a Database Schema?

Database Schema refers to a structure that represents relationships among data and defines how information is stored in a database. Without a proper schema, it is easy to drift away from the objective considering the scale of big data projects. As schema also represents the relationship among tables, different databases have different schema designs to support varying business requirements.

In short, Database Schemas are essential to do the following:

• Consistent formatting.
• Maintaining unique primary and foreign keys.

Importance of Database Schema Design

A Schema organizes data into Tables with appropriate Attributes, shows the interrelationships between Tables and Columns, and imposes constraints such as Data types. A well-designed Schema in a Data Warehouse makes life easier for Analysts by:

• removing cleaning and other preprocessing from the analyst’s workflow
• absolving analysts from having to reverse-engineer the underlying Data Model
• providing analysts with a clear, easily understood starting point for analytics

In other words, a well-designed Schema clears the way to faster and easier creation of Reports and Dashboards.

By contrast, a flawed Schema requires Data Analysts to do extra modeling and forces every Analytics query to take more time and system resources, increasing an organization’s costs and irritating everyone who wants their analytics right away.

Schemas are used to specify data items in both data sources and data warehouses in the Data Analytics field. However, Data Source Schemas aren’t created with Analytics in mind, whether they’re databases like MySQL, PostgreSQL, or Microsoft SQL Server, or SaaS services like Salesforce, Facebook Ads, or Zuora.

The SaaS apps, for example, may offer some broad analytics features, but they only apply to the data from that particular app. Users also have no control over SaaS Schemas, which are established by the developers of each program.

When enterprise data is duplicated to a Data Warehouse and linked with data from other applications, it becomes more useful – and enterprises get to build these Data Architectures.

How to Design a Database Schema?

Database schemas define a database’s architecture and help to ensure database fundamentals such as the following:

• The data is formatted consistently.
• Every record entry has a distinct primary key.
• Important information is not omitted.

A database schema design can exist as both a visual representation and as a collection of formulas or use constraints that govern a database. Depending on the database system, developers will then express these formulas in different data definition languages.

For example, despite the fact that the leading database systems have slightly different definitions of what schemas are, the CREATE SCHEMA statement is supported by MySQL, Oracle Database, and Microsoft SQL Server.

Suppose you want to create a database to store information for your company’s accounting department. This database’s schema could outline the structure of two simple tables:

A) Table1

• Title: Users
• Fields: ID, Full Name, Email, DOB, Dept

B) Table2

• Title: Overtime Pay
• Fields: ID, Full Name, Time Period, Hours Billed

This single schema includes useful information such as:

• Each table’s title
• The fields contained in each table
• Table relationships (for example, linking an employee’s overtime pay to their identity via their ID number)
• Any other relevant information

These schema tables can then be converted into SQL code by developers and database administrators.

Best Practices for Database Schema Design

It is essential to follow these practices to have a vantage point about an ideal database schema design.

• Naming conventions: To make your database schema designs most efficient, define and use appropriate naming conventions. While you can choose a specific style or follow an ISO standard, the most important thing is to be consistent in your name fields.
• Avoid using reserved words in table names, column names, fields, and so on, as this will almost certainly result in a syntax error.
• Use of hyphens, quotes, spaces, special characters, and so on will result in invalid results or will necessitate an additional step.
• For table names, use singular nouns rather than plural nouns (i.e. use StudentName instead of StudentNames). Because the table represents a collection, the title does not need to be plural.
• Remove unnecessary wordings from table names (for example, Department instead of DepartmentList, TableDepartments, etc).
• Security: Data security begins with a well-designed database schema. For sensitive data, such as personally identifiable information (PII) and passwords, use encryption. Instead of assigning administrator roles to each user, request user authentication for database access.
• Documentation: Database schemas are useful long after they are created, and they will be viewed by many other people, so good documentation is essential. Document the design of your database schema with explicit instructions, and include comment lines for scripts, triggers, and so on.
• Normalization: In a nutshell, normalization ensures that independent entities and relationships are not grouped together in the same table, which reduces redundancy and improves integrity. Use normalization as needed to improve database performance. Over-normalization and under-normalization can both lead to poor performance.
• Expertise: Well understanding and recognizing your data and the attributes of each element aid in the development of the most effective database schema design. A well-designed schema can allow your data to grow at an exponential rate. As you continue to collect data, you can analyze each field in relation to the others in your schema.

Understanding the Data Model

Understanding the underlying Data Model is the first and most critical step in using data from an Application. Since the world consists of organizations, individuals, transactions, and other common business ideas, every SaaS program automatically comprises a representation of the world. Making sense of the data requires knowing which data columns match which real-world equivalents. 

When dealing with an in-house Database, Developers and Data Engineers are likely to be able to describe the model. If you don’t have a team of developers and Data Engineers, do not worry, in this case, you must follow the vendor’s documentation and APIs while using SaaS Apps.

What is Data Normalization?

Normalization is an approach for Relational Database Schema Design.

Normalization aims to get rid of duplicate, redundant, and derived data values. A database administrator can normalize the logical structure of a data model to create a schema. The end result of the Normalization process is a Database Schema Definition, which is a collection of tables and columns known as Fields.

Some of the Fields in the Database Schema Definition are Key Fields, which means they are unique and can be used to build indexes that make it easier to store and retrieve records. Furthermore, these tables are linked to one another, representing Relational Database Management Systems.

What are Entity-Relationship Diagrams?

An Entity Relationship Diagram (ERD), also known as an Entity-Relationship Model, is a graphical representation of relationships between people, objects, places, concepts, or events in an information technology (IT) system. You can build them by drawing them or by using a variety of software tools. An ERD employs data modeling techniques to assist in the definition of business processes and as the foundation for a relational database.

Here’s an example of a Logical Database Schema, showcasing tables, fields, and primary keys.

The above image is an ERD that illustrates the tables, fields, interrelationships, and keys between different tables.

A Primary Key in a normalized database denotes the basic entity that the table represents and uniquely identifies each row in that table.

In a customer table, for example, the primary key is likely to be a customer ID, and the table would likely contain information such as a customer’s name, address, credit card number, and so on. Some columns are called as Foreign Keys. A Foreign Key is a column or set of columns in one table that refers to primary key columns in another. For example, an employee record might include a foreign key based on the employee’s Social Security number, which is the primary key in an employee earnings table.

The two types of keys link the entity represented by the primary key to another entity represented in a different table. In the ERD, key fields are represented by special symbols.

You can represent three types of relationships with the help of Primary and Foreign keys that are as follows:

1) One-to-one

In one-to-one relationships, only two entities can map onto each other; no other elements can. A real-world example would be Social Security numbers, which can only be assigned to one person at a time.

2) One-to-many

One entity in one table can correspond to multiple records in another table, but not the other way around. A list of ice cream flavors sold by a company and the customers who have ordered products that feature them is an example of one-to-many. That information could be used by a food retailer to determine a customer’s favorite flavor. That business insight can then be used to recommend new ice creams with that flavor as they hit the market.

The business value of the one-to-many database is thus demonstrated: identifying commonalities in user behavior and the data it generates, and aligning them with revenue-generating actions.

Each flavor can have a large number of customers, but each customer only has one favorite that stands above all others. When you come across a nested object with a one-to-many relationship to the main table, it is converted into a separate table.

3) Many-to-many

These relationships are represented in join tables. The composite primary key in a join table consists of the primary keys of the two related entities. For instance, a person’s shopping habits might bring them to many stores, and each store will have many customers.

Types of Database Schemas

Database schemas themselves are broadly divided into the following categories:

• Physical Database Schema
• Logical Database Schema
• View Database Schema
• Star Schema
• Snowflake Schema

Physical Database Schema

The physical database schema represents how data is stored on disk storage or data target. Physical schema is the lowest form of abstraction with regards to the schema.

It acts as the foundation for other types of schema to create relationships and indexes. Therefore, a physical schema usually indicates the storage allocation, which is defined in terms of GBs or TBs.

Logical Database Schema

A logical schema is the conceptual model of the database. It is platform agnostic and primarily focuses on business entities while creating relationships among tables. At the logical level, the data stored physically is illustrated as attributes, which can then be given meaning structure to simplify writing, reading, and updating of data.

View Database Schema

It can be defined as the design of the database at the view level, which generally describes end-user interaction with database systems. At the view level, a user can interact with the system using an interface. Users are not aware of where and how data is stored.

Star Schema

A star schema is a multi-dimension model used in data warehouses to supports advanced analytics. The strat schema contains a central fact table that is connected with several dimensional tables. Although simple to use, star schema takes a lot of space since dimensional tables do not link to sub-dimensional tables, limiting the extendability of data.

Snowflake Schema

Similar to a star schema, a snowflake schema is also a multi-dimension model used in data warehouses to support advanced analytics. Although both schemas organize the tables around a central fact table, the dimensional tables in the snowflake schema can further connect to sub-dimensional tables. The advantage of a snowflake schema is that less duplicate data is stored than in an equivalent star schema.

Database Design Schema Example

Here are the 5 key Database Design along with the Schema Example:

• Schema Example: E-Commerce Transaction
• Schema Example: Online Banking
• Schema Example: Hotel Reservation
• Schema Example: Restaurant Booking
• Schema Example: Financial Transaction

Schema Example: E-Commerce Transaction

Take the example of a customer on an e-commerce website. Two important components in a schema are the primary key and the foreign key. When generating an ER (entity-relationship) diagram, like the one shown above, the object’s primary key can be the IDs, which uniquely identifies the entry in a table. The foreign key, which is the primary key for another table, links the relationship from one table to the next.

SQL schemas are defined at the logical level, which is typically used for accessing and manipulating data in the tables. SQL servers have a CREATE command to create a new schema in the database.

The following creates a schema for customers, quantities, and price of transactions:

CREATE TABLE customer (
 id INT AUTO_INCREMENT PRIMARY KEY,
 postalCode VARCHAR() default NULL,
)
 CREATE TABLE product (
 id INT AUTO_INCREMENT PRIMARY KEY,
 product_name VARCHAR() NOT NULL,
 price VARCHAR() NOT NULL,
)

Schema Example: Online Banking

The following is a sample code of creating schemas like above with regards to online banking:

CREATE DATABASE
-- Table structure for table `account_customers`
DROP TABLE IF EXISTS `account_customers`;
CREATE TABLE `account_customers` (
  `Account_id` int(10) unsigned NOT NULL,
  `Customer_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`Customer_id`,`Account_id`),
  KEY `fk_Accounts (`Customer_id`),
  KEY `fk_Accounts1_idx` (`Account_id`),

Schema Example: Hotel Reservation

The above schema can be modified based on business rules such as number of requests per customer, number of assignments by admin, multiple rooms on the same booking date, payment types, etc.

Here is a sample code of creating the schema:

CREATE DATABASE example;
USE example;
DROP TABLE IF EXISTS customer;
CREATE TABLE customer (
 id INT AUTO_INCREMENT PRIMARY KEY,
 postalCode VARCHAR(15) default NULL,
)
DROP TABLE IF EXISTS product;
CREATE TABLE product (
 id INT AUTO_INCREMENT PRIMARY KEY,
 product_name VARCHAR(50) NOT NULL,
 price VARCHAR(7) NOT NULL,
 qty VARCHAR(4) NOT NULL
)
….
….

Schema Example: Restaurant Booking

In this schema, a unique id can be given to a customer. It can be read as ID or customer_id. Similarly, the user table, ingredient, and menu will be incorporated with business rules. A sample code to generate schemas like the one shown above:

CREATE TABLE `restaurant`.`user` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `fName` VARCHAR(50) NULL DEFAULT NULL,
  `mobile` VARCHAR(15) NULL,
  `email` VARCHAR(50) NULL,
…..
….
  PRIMARY KEY (`id`),

Schema Example: Financial Transaction

The above Schema Example represents a star-type schema for a typical financial transaction. As discussed in a star schema, you can see that this design looks clean and easy to interpret for future collaborations across teams. The transaction table is connected to the table of account holders as well as the banking staff who are at the helm of the transaction.

CREATE DATABASE example;
USE example;
DROP TABLE IF EXISTS customer;
CREATE TABLE customer (
id INT AUTO_INCREMENT PRIMARY KEY,
CurrencyCode VARCHAR) default NULL,
)
DROP TABLE IF EXISTS product;
CREATE TABLE product (
id INT AUTO_INCREMENT PRIMARY KEY,
GENERAL_LEDGER_ CODE VARCHAR(50) NOT NULL,
price VARCHAR(7) NOT NULL,
qty VARCHAR(4) NOT NULL
)
……
……

Designing Schema Example: Key Practices

A good schema facilitates optimal performance at scale. Though a design is dependent on the use case, a few common practices apply to almost all database designs:

• Have Good Naming Standards
• Use Normalization to Tackle Redundancy
• Fix the Right Number of Tables
• Avoid Nulls
• Have Proper Documentation
• Protect Data Integrity
• Use Stored Procedures to Access Data

1) Have Good Naming Standards

Names are the first and most important line of documentation for the application. Appropriate naming makes database design schemas most effective. The names enable you to identify the purpose of an object and simplify collaboration. Keep the following in mind while naming:

• Consistency is the key while naming. 
• Try not to use SQL Server reserved words in table names, column names, and fields because it can result in a syntax error. 
• Avoid using hyphens, quotes, spaces, and special characters because it isn’t valid or will require an additional step. 
• Avoid unnecessary prefixes or suffixes for table names.

2) Use Normalization to Tackle Redundancy

Redundancies are a common sighting in database designs. The tricky part here is that these redundancies can be both good or bad depending on the use case. This is where normalization comes to the rescue. Database normalization is the process of structuring a database under a series of normal forms to reduce data redundancy.

Both over-normalization and under-normalization will result in worse performance. Consequently, the decision to maintain or eliminate a redundancy is made by comparing the cost of operations that involve the redundant information and the storage needed.

3) Fix the Right Number of Tables

A good database will only have as many tables as the application requires not more, not less. Even though there is no single ‘right’ number of tables for all databases, keeping tables down to representing one “thing” is considered to be effective as the changes will then only affect one table. This will, in turn, reduce the rework as one proceeds.

4) Avoid Nulls

This can be done by specifying NOT NULL whenever one wants to keep empty information. Avoid nulls or use them only when you truly need them since attributes with null values cannot form primary keys.

5) Have Proper Documentation

This is an extension of having an appropriate naming etiquette. Documentation helps collaboration across teams and assists new programmers to come on board easily. Good documentation consists of definitions on its tables, columns, relationships, and even default and checks constraints.

6) Protect Data Integrity

Fundamental business rules should be located in a database. Rules such as nullability, string length, assignment of foreign keys, and so on, should all be defined in the database.

When base rules are defined in the database, they can never be bypassed, and queries can be written without ever having to worry whether the data adheres to the base business rules. Use SQL facilities to maintain data integrity. 

7) Use Stored Procedures to Access Data

Stored procedures allow database development in an effective way for collaboration across teams and development between the database and functional programmers.

These procedures give the database professionals the ability to change the characteristics of the database code without much overhead. In addition, they can also provide granular access to the system.

Creating Relationships between Entities

You’re ready to study the associations between your Database Tables now that they’ve been transformed into tables. The quantity of components that interact between two linked tables is referred to as cardinality. Identifying the cardinality aids in ensuring that the data has been divided into tables as efficiently as possible.

Each entity has the ability to have a relationship with every other entity, however, these relationships normally fall into one of three categories:

1) One to One Relationship

A one-to-one relationship exists when there is only one instance of Entity A for every instance of Entity B. (often written 1:1). In an ER diagram, draw a line with a dash on each end to represent this type of relationship:

A 1:1 relationship normally suggests that you’d be better off integrating the data from two tables into a single table unless you have a solid reason not to.

Under certain cases, though, you may want to create tables with a 1:1 relationship. You can transfer all of the descriptions into their own table if a field with optional data, such as “description,” is blank for many of the records, saving empty space and Boosting Database Performance.

You’d have to include at least one similar column in each table, most likely the primary key, to ensure that the data matches up appropriately.

2) One to Many Relationships

When a record in one table is linked to several entries in another, these relationships form. A single customer, for example, may have placed many orders, or a patron may have multiple books checked out from the library at the same time. The “Crow’s foot notation” is used to denote one-to-many (1:M) relationships, as in this example:

Simply add the primary key from the “one” side of the relationship as an attribute in the other table when creating a Database to build a 1:M relationship.

A foreign key is a primary key that is listed in another table in this way. To the child table on the opposite side of the connection, the table on the “1” side is regarded as a parent table.

3) Many to Many Relationships

A many-to-many (M:N) relationship exists when many entities from one table can be linked to multiple entities from another table. This might happen with students and classes because a student can take multiple classes and a class can have a large number of pupils.

These connections are shown in an ER diagram.

Unfortunately, this type of relationship cannot be directly implemented in a Database. You must instead divide it into two one-to-many relationships.

Create a new entity between those two tables to accomplish this. If sales and products had a M:N relationship, you could title the new entity “Sold Products,” because it would display the contents of each sale.

With sold products, both the sales and products tables would have a 1:M connection. In various models, this type of go-between object is referred to as a link table, associative entity, or junction table.

Each record in the link table corresponds to two entities from nearby tables (it may include supplemental information as well). A link table between students and classes, for example, would look like this:

Multi-dimensional Data

Some users, particularly in OLAP Databases, may desire to access many dimensions of a single type of data. They might, for example, wish to know about sales by client, state, and month. It’s ideal to build a core fact table that other customer, state, and month tables can refer to in this circumstance, such as this:

Data Integrity Rules

You should also set up your Database so that data is validated according to the rules. Some of these rules are enforced automatically by many Database Management Systems, such as Microsoft Access.

• The main key can never be NULL, according to the Entity Integrity Rule. None of the columns of a key that is made up of many columns can be NULL. Otherwise, the record may be unable to be individually identified.
• Each foreign key specified in one table must be matched with one primary key in the table it references, according to the Referential Integrity Rule. If the primary key is changed or removed, the changes must be reflected everywhere that key is referenced in the Database.
• Integrity Rules for Business Logic ensure that the data falls inside a set of logical constraints. For example, an appointment would have to take place during regular business hours.

Conclusion

Database Design Schema is essential for organizations to enable effective ways of data storage and retrieval. A proper schema can be the difference between how flexible a database can be during varying needs. However, along with flexibility, organizations must focus on optimizing for speed to cater to critical business requirements. This blog talked about the different Database Design & Schema Example before diving into the best practices to follow for these Database Design & Schema Example. You can have a look at Star and Snowflake Schema Analytics.

Visit our Website to Explore Hevo

Extracting complex data from a diverse set of data sources can be a challenging task and this is where Hevo saves the day! Hevo offers a faster way to move data from Databases or SaaS applications into your Data Warehouse to be visualized in a BI tool. Hevo is fully automated and hence does not require you to code.

Want to take Hevo for a spin?

Sign Up for a 14-day free trial. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

Share your experience of understanding the Database Design & Schema Example in the comments section below!