5 Database Schema Design Example: Critical Practices & Designs

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. Consequently, Database Schema Design Examples and the Database Schema Example have gained prominence over the years to help users understand databases easily.

With scale, these specifics become challenging for the end-user as combining the different data sources 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 discusses the Database Schemas and their types, eliciting the 5 key Database Schema Example. It wraps up with the key practices to follow for optimal performance.

Database Design Schema Examples

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

• 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
)
……
……

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, even though 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.

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 fr

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:

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.

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 consistency 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 should not 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 developing 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.

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 schema Examples before diving into the best practices to follow for this Database Design and schema Example. You can have a look at Star and Snowflake Schema Analytics for a better understanding.

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 Hevo 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!