7 steps to create a successful logical database schema design

I remember when I was at the university and I took the class "Data Management." At that time, learning database schema design was challenging because we only had a blackboard, sheets of paper, pencils and a teacher trying to explain something so abstract that we had no idea what a database was, much less a database schema.

With today’s resources, software and tools, we can understand these concepts more easily. But, regardless of the software we use to implement our database schema physically, if we want to use our time efficiently, it is necessary to create a good conceptual model of our project. For this reason, I invite you to discover the 7 steps that I learned over the years to design logical database schemas.

Step 1: Define objectives for database schema design

The first step in the database schema design process is to define its purpose. Each database schema is created for a specific purpose, such as to solve a business specification, handle daily business transactions, or as part of an information system.

It is essential to pay attention to the purpose of the database schema because it will help us properly design its structure. In this initial stage, we can also define a list of objectives representing the tasks that users will perform with the data from the database schema.

Step 2: Analyze the current database

The second step is to analyze the database, if one already exists. Depending on the organization, the database may be electronic and accessible by users, or it may be paper-based, that is, it contains forms, file folders, index folders and all kinds of documents that contain the data.

Regardless of the type of database in the organization, analyzing it provides us with valuable information about how our organization currently uses and manages data. In this process, it is also necessary to analyze how the organization collects and presents data. In this step, we will examine how the organization collects data via forms and how it presents the data through reports and in a user interface.

Another essential part of the analysis is to conduct interviews with users and management to identify how each group interacts with the database. The type of questions and their answers will have a significant impact on the final structure of our database. We must take the time to conduct full interviews to ensure that the information we obtain meets the information needs of our organization.

The information collected from the database analysis and interviews forms the basis of an initial list of fields. While you may think it better to first identify the entities or topics for tables, having a list of the main fields helps identify the topics from a more general perspective.

The initial list represents the organization's fundamental data requirements and provides a starting point for designing a new database schema. The initial field list should be sent to users and managers for review and feedback. Then, it can be modified as appropriate.

Step 3: Create the data structure for the database schema design

The third step in designing a database schema is defining the tables and fields, setting the keys, and defining each field's specifications.

Tables are the first object that we define in the database. From the objectives we established in step 1 and the list of requirements that we developed in step 2, we represent the topics as tables and associate the fields with each topic. After we’ve created these associations, check that each table represents a single topic and does not contain duplicate fields. Also review each field to determine that it contains unique data, that is, it stores a unique value. Move or delete fields that don't represent different characteristics of the topic that the table represents.

Check that each table contains all the necessary fields so that the table's structure is correctly defined. Then, proceed to define the primary key of each table. The primary key is the field that uniquely identifies each record within a table.

The final activity in this step is to determine the specifications for each field in the database schema. It’s useful to conduct additional interviews to help identify the specific characteristics of the fields that are important to stakeholders. Then, we can define and document the specifications for each field. When this step is complete, we can again review the structure of each table and the specifications of each field with the stakeholders to detect possible improvements.

Step 4: Determine and establish the table relationships of the database schema

Designing a good database schema involves establishing relationships between tables, and that is the focus of the fourth step. We return to conduct interviews with stakeholders to identify the relationships between the tables. During the process of designing a good database schema, communication is essential. Who better than the users to understand the different relationships between the tables?

Once we have identified the relationships, we need to establish logical connections through a primary key or linking tables. This action depends on the type of relationship that we identify between the two tables. These relationships are expressed as one-to-one, one-to-many or many-to-many.

We must also determine the degree of participation of the tables in each relationship. The degree of participation indicates the minimum number of records that a given table must have associated with a single record in the related table and the maximum number of records that the table can have associated with a single record in the related table. In some cases, these characteristics will be obvious due to the nature of the data stored in the tables. In other cases, we will base the participation characteristics on specific business rules.

Step 5: Determine and define the business rules of the database schema

The fifth step in the process of designing a database schema is determining and defining the business rules. At this stage, we will also conduct interviews to identify limitations in the database. The way the organization views and uses data determines a set of constraints and requirements for the database schema design.

For example, a user who works with an order processing database is very clear about the specifications, such as the fact that the shipping date must follow the order date; or that there must always be contact information in the order and a shipping method must be established.

After conducting the interviews, we must document the specifications as business rules, and whether they are specific to fields or relationships. Then we can implement different validation tables, commonly called lookup tables, as needed to support certain business rules. For example, our organization stores information in the "product categories" field as Books, CDs, DVDs, Clothing, Shoes, Jewelry & Watches. We can use validation tables to ensure the consistency and validity of the values stored in those fields. It's important to realize that implementing validation tables also allows us to change those business rules if the organization needs to.

Step 6: Determine and define the database schema views

Now, through stakeholder interviews, we identify the ways that users work with the data, and the information each type of user needs. Some may need detailed information, and others may only need summary information to help them make strategic decisions for the organization.

Each group of users must access the information in very specific ways, and for this, we define the criteria for each view. For example, we can create a view to show all customers located in California or another view that shows the total number of sales made in June of the current year.

Step 7: Check the data integrity of the database schema

The last step in the process of designing a database schema involves reviewing the final structure of the database to verify the integrity of the data. First, we review each table to ensure it meets the criteria for a properly-designed table, and we check the fields within each table to ensure they have the proper structure. Then we resolve any inconsistencies or issues we find and review the structures one more time.

For the final review:

• Check the integrity at the table level
• Review and verify the specifications for each field
• Make any necessary improvements to the fields, verifying integrity at the field level
• Review the validity of each relationship
• Review the integrity of the relationship to ensure that there are matching values between the shared fields and there are no problems with inserting, updating, or deleting data in any of the tables within the relationship
• Review the business rules and confirm the restrictions imposed.

Conclusion

Once we have completed the entire database schema design process, we are ready to implement the logical structure of our database schema in an RDBMS program. However, it is important to note that this process is never really finished because the database structure will always need refinement as our organization grows.

Related information

Blog: Getting to Know the Toad for Oracle Schema Browser

Blog: What is database schema? Objects, owners, tables and indexes

Blog: How to compare two database schemas in Oracle using Toad®

Blog: How to compare multiple database schemas in Oracle using Toad. 

Quest blog: Using Database Schemas in SQL Server

Video: Toad for Oracle – Compare DB environments with multiple schemas

Blog: Compare and Sync Multiple Tables with Toad

Blog:How to compare database schemas with Toad® DevOps Toolkit 1.3

Help your colleagues

If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post. Thanks!