Relational Database Schema Design Overview (2023)

Relational Database Schema Design Overview (1)

It is about time I started reviewing my SQL and database knowledge. I started off by reviewing SQL queries as simple as selecting rows from a table, but as I was going through these queries, I felt like I was not touching upon everything important to relational databases. In the back of my head, I was thinking there are relationships, 1NF, 2NF, etc. … But I wasn’t sure until I came across a database schema problem today. Doh! I forgot what it takes to create a database schema!

What is a relational database? A relational database is defined as a database structured to recognize relations among stored items of information according to Google search. You can represent data of all sorts through a relational database, such as a grocery store’s inventory to a realtor company and their houses.

The way a relational database works is by storing information in tables, where each table has its own rows and columns. A row represents a record (or tuple), while a column represents a field (or attribute). I will provide an example of a basic table.

Relational Database Schema Design Overview (2)

Now, after seeing this example, you might think a database looks like a spreadsheet. However, what makes a database relational is the fact that there are relationships between the tables. This enables a relational database to efficiently store large amounts of data, while retrieving selected data.

Relational Database Schema Design Overview (3)

SQL (pronounced either as “sequel” or “ess que el”) stands for Structured Query Language. This language was developed to work with relational databases. There are many popular adaptations of SQL that are specific to certain relational database management systems (RDBMS), such as PostgreSQL, MySQL or mSQL. We will just be using standard SQL in this article.

(Video) Introduction to Relational Data Model

What is essential to a good database design?

  • Reduces redundancy: divide information carefully among tables to eliminate data redundancy. Duplicate data wastes space and can lead to inconsistency.
  • Provides access with information a user needs to join tables together. (Use good primary keys, while creating proper relationships between tables.)
  • Ensures data accuracy and integrity.
  • Accommodates your data processing and reporting needs.

You are the designer — make sure you make the right decisions by using these guidelines to create the best relational database you can.

  1. Define the purpose of the database — You want to find and gather requirements for this database. Determine its objective. You can help determine what kind of information you need to record into the database by sampling queries and what results you want from the query. From here, gather the information you need, divide them into individual tables. Determine what the columns (fields) of each table will be. Remember, do not repeat information.
  2. Figure out the Primary Key for each table — A primary key is a field that uniquely identifies a row. A primary key can be multiple fields if necessary, in which this is known as a composite key, while a primary key using only one field is known as a simple key. For the grocery list above, the Primary Key is the column grocery_id. I chose grocery_id as the primary key as it is unique to every grocery item, is of integer type, and is simple. In light of my decision, please remember these guidelines for choosing a primary key:
  • Make sure each row has a unique key — you cannot have duplicate values for the primary key of separate rows.
  • Must have a value! Can never be null.
  • Keep it simple if you can.
  • Use an integer type primary key.

3. Determine the relationships among the tables — if you only have one table, it is pretty useless to have a database — consider a spreadsheet instead. Now, if you have multiple tables, you must determine the relationship between tables. There are many types of relationships between tables: one-to-one, one-to-many, many-to-many. We will briefly describe these relationships below, with examples.

  • One-to-one: In a one-to-one relationship, only one row of a table is linked to at most one row on the other table. In this case, a one-to-one relationship is useful to store data that is optional. For example, a grocery list item can map to a grocery item details table that provides more information on the item. If the grocery item does not have this data available, there won’t be an entry for that grocery item. Check out the tables below as an example. Both tables can be joined with their common column grocery_id, which happen to be the primary key of both tables.
Relational Database Schema Design Overview (4)
Relational Database Schema Design Overview (5)
  • One-to-Many: In a one-to-many relationship, one row of one table can link to many rows in a table. A great example of a one-to-many relationship is an AuthorsList and BooksList. An author can have many books written, and in this case let’s keep it simple by imagining each book having only one author. The advantage of a one-to-many relationship is to store the frequently used information in the parent table (the table with “one”), while referencing this information many times in the child table (the table with “many”). In the child table, there should be a foreign key, which is the parent table’s primary key, that is used to reference back to the parent table. In the example below, the foreign key is the author_id.
Relational Database Schema Design Overview (6)
Relational Database Schema Design Overview (7)
  • Many-to-Many: In a many-to-many relationship, one or more rows of one table can link to 0, 1 or many rows in the other table. To implement this relationship, we must use a mapping or intermediary or junction table.

We will use a grocery store as an example. In a grocery store, you have a list of products (grocery list of the store) called Products with primary key product_id, and each customer has their own order (or transaction) full of groceries, called Transactions with primary key transaction_id. A grocery can be purchased by 0, 1 or more customers, and a customer can purchase 0, 1 or more groceries. To map this, we use a junction table named ProductTransaction using both primary keys product_id and transaction_id to map a transaction (or customer) to a product, and map a product to a transaction.

Relational Database Schema Design Overview (8)
Relational Database Schema Design Overview (9)
(Video) What is a Relational Database?

4. Refine the Design — Ask yourself these questions: do you have enough columns to represent your data? Could you derive a column from a combination of columns? Are you entering duplicate information? Do you have any empty fields in individual records? Can a large table be split into two? There are more questions you may ask about your database schema, but to achieve a refined design, you must normalize your tables.

Normalization Rules:

  • First Normal Form (1NF): A table follows first normal form if for every row and column intersection (a cell in a table) in the table, there exists a single value and never a list of values. This is known as the atomic rule. Use the one-to-many relationship to follow 1NF.
  • Second Normal Form (2NF): A table follows second normal form if it is 1NF and every non-key column is fully dependent on the primary key. This also applies when a primary key has multiple columns, every non-key column should still depend on the entire set and not part of it.

This example does not follow 2NF:

  • Order ID (PK)
  • Product ID (PK)
  • Product Name

Since Product Name depends on Product ID but not Order ID.

  • Third Normal Form (3NF): A table is 3NF if it is 2NF and the non-key columns are independent of each other.

This example does not follow 3NF:

  • Grocery ID (PK)
  • Name
  • Price
  • Discount

Since Discount is dependent on Price, another non-key column.

Lastly, there are integrity rules to ensure integrity of your information:

  • Entity Integrity Rule: The primary key cannot contain NULL! Otherwise, it cannot uniquely identify the row. This includes for multiple column’d primary keys as well, none of the columns can be NULL.
  • Referential Integrity Rule: Each foreign key value must be matched to a primary key value in the table referenced (or parent table). In turn, this means that you can only insert a row with a foreign key in the child table only if that value (of the foreign key) exists in the parent table. If the value of the key changes in the parent table, all rows in the child table(s) should also change the value of the foreign key referring to the parent table. This can be handled by either not allowing change, cascading the change, or set the key value in the child tables to NULL.
  • Business logic integrity: These are validation rules to ensure for correct values (5 digit zip codes, validating credit card number, etc).

There are many steps to creating a “good” database structurally. First, you need to understand the needs of your database. What information do you need to store? How can you split this information up into separate tables? What are the primary keys for each table? These are very important questions to ask before jumping into creating a database.

You use the information above to create your tables. The tables then need to form relationships with one another. If there are many-to-many relationships, you will need a junction table. Lastly, after establishing relationships, you need to normalize your tables. Make sure that all tables are 3NF, and follow integrity rules to ensure the safety of your data.

Voila, you now have yourself a good database schema design! Below are the materials I used to create this article.

(Video) Informal design guidelines for relational schemas (with examples)

References:

FAQs

What is relational schema design? ›

A relational schema is a set of relational tables and associated items that are related to one another. All of the base tables, views, indexes, domains, user roles, stored modules, and other items that a user creates to fulfill the data needs of a particular enterprise or set of applications belong to one schema.

What are the top 3 principles you use while designing schemas? ›

Best Practices for Database Schema Design (Beginners)
  • Know your usage patterns. There are two primary ways a database is used. ...
  • Naming Conventions. I don't want to dwell on naming conventions because everyone has their own style and may choose to adhere to an ISO standard (ISO-11179, etc.) or not. ...
  • Use Constraints.
Mar 28, 2011

What 3 questions should be asked when designing a new database? ›

Be sure you ask your team these questions:
  • What are your preferred programming languages?
  • What programming language drivers does the database support?
  • Does this database need to connect to any of your business intelligence tools?
  • What about other databases? ...
  • How is data going to be ingested into the database?
Dec 7, 2021

What are 3 types of relationships in relational database design? ›

There are 3 different types of relations in the database:
  • one-to-one.
  • one-to-many, and.
  • many-to-many.
Jan 22, 2020

What are the 4 basic design rules? ›

Effective design centres on four basic principles: contrast, repetition, alignment and proximity. These appear in every design.

What are the 5 principles of good design? ›

Principles of design
  • Balance.
  • Alignment.
  • Proximity.
  • Repetition.
  • Contrast.
Feb 27, 2019

What are the five 5 major components of a database? ›

The five major components of a database are hardware, software, data, procedure, and database access language.

What are the six phases in designing relational databases? ›

Learn how to design databases in six easy steps
  • Create implementable database models.
  • Create Crow's Foot Relational Model (R-M) diagrams.
  • Create Entity Relationship Model (E-R) diagrams.
  • Case studies to reinforce the instructions.
  • Easy to follow, step by step instructions.

What are the 3 main phases in the database design methodology? ›

The methodology is depicted as a bit by bit guide to the three main phases of database design, namely: conceptual, logical, and physical design.

What are the four 4 significant objects on making a database? ›

Databases in Access are composed of four objects: tables, queries, forms, and reports.

What are the five steps in designing a database? ›

Database Design Steps

Define the data requirements for various stake holders. Define the database entities in terms of tables. Identify and define the attributes for each database entity. Specify the primary and foreign key constrains for each relational table.

What are the 4 informal guidelines for database design? ›

  • Informal Design Guidelines for Relation Schemas. ...
  • - spurious tuples are those they represent spurious information that is not valid. ...
  • FUNCTIONAL DEPENDENCIES. ...
  • NORMALIZATION OF RELATIONS. ...
  • 1) First normal form (1NF) ...
  • 2) Second normal form(2NF) ...
  • 3) Third Normal Form(3NF)

What are the 4 relational models? ›

Definition. The relational models theory describes the four fundamental forms of social relationships: communal sharing, authority ranking, equality matching, and market pricing.

What are the three 3 main categories of relational database constraints? ›

Mainly Constraints on the relational database are of 4 types:
  • Domain constraints.
  • Key constraints.
  • Entity Integrity constraints.
  • Referential integrity constraints.
Jun 1, 2021

What are 4 different types of DBMS relationships? ›

Three types of relationships can exist between two entities, which are given below and also discussed in this article with examples.
  • One-to-One relationship.
  • One-to-Many relationship or Many-to-One relationship.
  • Many-to-Many relationship.
Jun 22, 2022

What are the 5 types of schemas? ›

There are many types of schemas, including object, person, social, event, role, and self schemas.

What are the 5 schemas? ›

The Five Schema Domains Defined
  • Abandonment/Instability.
  • Mistrust/Abuse.
  • Emotional Deprivation.
  • Defectiveness/Shame.
  • Social Isolation/Alienation.

What are the 7 schemas? ›

How many schemas are there?
  • Connecting.
  • Orientation.
  • Transporting.
  • Trajectory.
  • Positioning.
  • Enveloping.
  • Enclosing.
  • Rotation.
Jan 8, 2020

What are the 3 levels of schema architecture? ›

Explain the three level schema architecture in DBMS?
  • External level.
  • Conceptual level.
  • Internal level.
Jul 3, 2021

What are the two levels of relational schemas? ›

The logical (or conceptual) level—how users interpret the relation schemas and the meaning of their attributes. The implementation (or storage) level—how the tuples in a base relation are stored and updated.

What is the purpose of database schemas? ›

Access and security: Database schema design helps organize data into separate entities, making it easier to share a single schema within another database. Administrators can also control access through database permissions, adding another layer of security for more proprietary data.

What is the 7 principles of design? ›

The seven principles of art and design are balance, rhythm, pattern, emphasis, contrast, unity, and movement. The elements of art and design are line, shape/form, space, value, color, and texture.

What is the golden rule of design? ›

A good interface should be designed to avoid errors as much as possible. However, if something goes wrong, your system should make it easy for users to understand and resolve the problem. Simple ways to deal with errors include displaying clear error notifications and descriptive hints to resolve the problem.

What is the 8 elements of design? ›

The elements, or principles, of visual design include Contrast, Balance, Emphasis, Movement, White Space, Proportion, Hierarchy, Repetition, Rhythm, Pattern, Unity, and Variety. These principles of design work together to create something that is aesthetically pleasing and optimizes the user experience.

What is relational schema with example? ›

For example, a relational schema for a bakery might have tables that state ingredients, recipes, types of baked goods, prices or customer information as attributes. In a relational database schema, attributes are defining characteristics that determine the items in a table.

What is a schema design? ›

Database schema design organizes the data into separate entities, determines how to create relationships between organized entities, and how to apply the constraints on the data. Designers create database schemas to give other database users, such as programmers and analysts, a logical understanding of the data.

What is meant by relational database design? ›

Relational database design (RDD) models information and data into a set of tables with rows and columns. Each row of a relation/table represents a record, and each column represents an attribute of data. The Structured Query Language (SQL) is used to manipulate relational databases.

What is relational design? ›

Relational design is preoccupied with design's effects, extending beyond the form of the design object and its attendant meanings and cultural symbolism. It is concerned with performance or use, not as the natural result of some intended functionality but rather in the realm of behavior and uncontrollable consequences.

What is the purpose of a relational schema? ›

A relational schema is a blueprint used in database design to represent the data to be entered into the database and describe how that data is structured in tables (called relations in relational schemas). The schema describes how those tables relate to each other.

Videos

1. Database Schema
(Neso Academy)
2. Database Design Course - Learn how to design and plan a database for beginners
(freeCodeCamp.org)
3. Relational Data Modelling | Schema Design Using Linkedin | Exclusive Tips & Tricks 2023
(SCALER)
4. Database Design Tutorial
(GoSparker)
5. Entity Relationship Diagram (ERD) Tutorial - Part 1
(Lucid Software)
6. Database Design Process
(Neso Academy)
Top Articles
Latest Posts
Article information

Author: Tyson Zemlak

Last Updated: 03/11/2023

Views: 6186

Rating: 4.2 / 5 (43 voted)

Reviews: 82% of readers found this page helpful

Author information

Name: Tyson Zemlak

Birthday: 1992-03-17

Address: Apt. 662 96191 Quigley Dam, Kubview, MA 42013

Phone: +441678032891

Job: Community-Services Orchestrator

Hobby: Coffee roasting, Calligraphy, Metalworking, Fashion, Vehicle restoration, Shopping, Photography

Introduction: My name is Tyson Zemlak, I am a excited, light, sparkling, super, open, fair, magnificent person who loves writing and wants to share my knowledge and understanding with you.