When database schema design is done well, it’s amazing. When it’s done poorly, database design can be a nightmare. What separates these two polar opposite database designs? Follow my steps for database schema design based on real world experience of designing database for many companies.

Database schemas are a key factor in the success of any software project. Here are some best practices, database schema design examples and sql database table design best practices for designing and laying out your database schemas.

Best practices for database schema design

In our last blog post, we covered some of the biggest mistakes you can make in designing your database schemas. (And if you need a database schema definition, look no further.) Let’s take a look at how you can make better choices to ensure that your database schema design helps users, data scientists, and data analysts find what they need and use it well.

1. Entity-relationship diagrams

As we mentioned in our last post, a good entity-relationship diagram is an important part of a relational database schema: It is a visual representation of the underlying data model contained to be sent from your source to your data warehouse. This includes tables, folder structure, business teams, data labels and so forth. The provenance of a data model can be complicated, and this diagram can help by illustrating the potential multitude of tables and interrelationships that support a particular data model. Finally, the diagram should also be a living document and reflect the current state of your databases as they change.

To see a good, concrete database schema example, look at the Klaviyo ERD and read about how we built the connector.

2. Schema flexibility

Your data schema should be flexible enough to evolve with your changing needs. And you should be able to make these updates with some understanding of the consequences of how your data infrastructure will change too. This is why you need to update your relationship diagrams, as mentioned above: Your data is a living, breathing organism.

3. Know your data’s purpose

While you don’t have to go full-out Tony Robbins, you should have a firm grasp of your data’s purpose in life. You’ll need to know what it will be used for and what business decisions it will enable in order to design the appropriate data structures, anticipate the volume of data queries, choose the best possible database engine and other environmental and management issues. A schema should satisfy multiple goals, including reducing redundant data, enforcing data consistency, ensuring data integrity and so forth.  

4. Understand the end game

A good way to determine purpose is to mock up some sample reports that your stakeholders will need. If you start with the end product in mind like this, you should have a better understanding of the shape of your data needs. This also applies to data dashboards. You want the dashboards not to just look pretty, but provide actionable insights that managers can use in their daily jobs.

5. Upfront planning

As we hinted at in our last blog post, the more effort you put into upfront planning, the easier it will be to produce the most effective database. Understand who the data consumers and the target audience are for the reports that will be generated from this data ahead of time. While it is great to be able to create ad-hoc queries and reports, your users still will benefit from creating a few samples as part of this planning effort to guide your plans.

6. Who designs your schema is important

Make sure your various data abstraction layers, application interface, and data feeds are useful to the data users and analysts that will produce reports and other data-oriented products. Engineers don’t typically approach issues the same way that data analysts do, so having both of these as part of your schema design team is important.

7. Indexing

If you have designed your schema properly, you will have just the right amount of indexing for the different types of queries used by your analysts. You can have too many indices or too few, and neither situation is optimal. Figuring out the right balance will take some effort and experimentation.

8. Clear, consistent naming conventions

You should label your fields, tables, and other data elements with meaningful names so that everyone who uses the data can figure out what these elements mean at first glance. This also means keeping these names consistent across your entire database. Don’t use system-reserved labels for column fields or table names. Don’t use hyphens or other punctuation marks that will just confuse things or require special programming to avoid errors. Keep your names short without any necessary modifiers. Finally, if it isn’t obvious what something is from its name, think about changing it to something else.

9. Think about data security from the beginning

The concept of “security by design” has been around for decades. If this term is unfamiliar to you, take a moment to review what it means and how you can implement it. Part of this design means not giving administrative rights to every user and every developer, but rather ensuring that each user has the right access level that is appropriate for their needs. An entire other topic is ensuring that your sensitive and confidential data is protected by the right amount of encryption and that it can’t be exported by malicious hackers.

10. Document your schema copiously

Because your schema is a living organism, you want to ensure that it has a happy and useful life long after the creators have moved on to other jobs or other employers. The best way to ensure this is to produce a careful and complete document that explains your choices, has plenty of comments in the code, and other information. Have you explained the relationship among the various fields in each table, or the relationship among your various tables?

database schema design examples

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

sql database table design best practices

Database design best practices

Reading time: about 9 min

Posted by: Lucid Content Team

Designing a database the right way requires some analysis of your data and planning around how you want to structure it. There are many different database types, models, and customizations you can use to achieve your goals. 

Here’s how to plan your database and start designing it. 

How will you use your database?  

Today, organizations are using data as part of their business intelligence gathering in end-customer products and services, for forecasting, and to inform real-time business decisions. Databases don’t have to be digital—technically, a notebook counts—but digital databases mean you can work with Big Data and use data analytics much more effectively. 

In the past, much of the data that businesses collected was discarded or not used in meaningful ways to drive business decisions. Think, for instance, of all of the retailers who collected purchasing data to process in-store sales at the checkout register and didn’t have an efficient way to keep point-of-sale data or perform useful analysis of it. 

Now we know that data can be extraordinarily valuable for organizations, and we have more and more means of leveraging and visualizing data than ever before. 

What does good database design look like? 

How you’re using the data and knowledge your organization collects is one important consideration when you’re developing your goals. Database design is usually dictated by how you’re using your data today and how your organization plans to use it in the future. If you already have a database with existing data, then you have to consider how you’ll migrate as well. 

For every database use case, there are different types of databases, database software, and specific designs. The database design you use today may not fit all of your needs tomorrow. This is why databases aren’t chosen randomly but represent a carefully-researched decision at most companies. 

Unsure of what database to choose? Have we got the article for you.See our tips

Good database design is driven by several core principles: 

• Minimize redundancy: To save resources, create an efficient database, and simplify how the database works, data redundancy is minimized and duplication is avoided.
• Protect accuracy: Your database should keep information accurate and reduce the likelihood of accidentally damaging information. 
• Be accessible: The business intelligence systems that need reading and writing access should have it. Your database should provide access while also protecting data security. 
• Meet expectations: Of course, you keep a database to fulfill a specific purpose—so the database design must successfully support your data processing expectations. 

Your database should take into consideration what stakeholders in your organization need from their data. For this reason, it’s a good practice to include them in your database design process. 

Determining your goals for your database

Bring in stakeholders

Who should you invite feedback from on your database design? Think about end-users within your organization, including team members, project managers, devs, and other internal stakeholders, as well as your external stakeholders such as business customers or power users. Before you get too far into mapping out your goals and beginning the design process, think about stakeholders who should be involved and how to involve them. 

This stakeholder involvement not only prevents possible backlash by avoiding designs that others in your organization would see as a bad fit. It also brings you more ideas, best practices, and experience to potentially draw from that can save resources and improve the outcome. 

Gather information to help with your decision  

Ask yourself some pointed questions to determine the database you need. First, though, you should start gathering information that will help you with this process and decision. 

• Forms: Collect the forms using data that will go in the database. 
• Processes: Review each process involved in collecting or processing data for the database. You’ll need to have these processes available for reference as you plan your database. 
• Types of data: Any data fields you’d gather and store in your database, such as customer contact information for a database of customers: name, email address, address, city, state, and zip code. Your data should be broken down into basic pieces, removing any complexity. 

SQL vs NoSQL

Structured Query Language (SQL) allows you to interact with a database and make meaningful use of its data. Often, databases are categorized as SQL or NoSQL (Not Only SQL). NewSQL has properties of both. There are unique pros and cons to these options, so think about how your database’s characteristics enable or restrict how you use them. 

SQL

Otherwise known as a relational database, SQL databases are made up of tables of data along with the relationships among the data fields. These are traditional databases, and they’re popular for many different database use cases, but they’re also difficult to scale vertically. You can horizontally scale SQL databases, but this isn’t appropriate for every database use. 

Today, many types of data need to be stored and managed in a more streamlined way—with databases that don’t have the same requirements and expectations associated with SQL and ACID compliance. 

One example of where SQL gets into trouble with large-scale data is with atomicity. A relational database can’t function well without restricting “write” activity and managing it carefully with bookkeeping in the background to ensure data integrity. As you scale, these management activities can be difficult to expand and adapt, which can be a problem for certain Big Data projects. 

NoSQL

As noted earlier, it’s “not only SQL” rather than “no SQL,” so you can have a NoSQL database with some relational components that are structured with SQL. NoSQL databases run the gamut in terms of how data is stored and structured. With NoSQL, though, you do have some component of your database that’s not managed by SQL. 

Data models

Aside from choosing SQL or NoSQL, you need to think about the data model you’ll use: 

• Relational database: All relationships are already defined in a relational database, connecting together tables with columns and rows of data. With this type of database, you can use your data in many different ways without rearranging it. This is great for many complex use cases involving situations where you need to store data with many different relationships, such as product names along with product information. 
• Hierarchical database: A one-to-many, tree-like data structure. For a hierarchy (hence the name), hierarchical databases make a lot of sense. You could create a database with department names, and each department can be associated with a list of employees who work there. 
• Network database: Like hierarchical databases, network databases can have a parent record associated with multiple child records. Network databases can also have multiple parents associated with a single child, however, adding flexibility for some uses. If you visualize a network database, it will look something like a net or web of interconnected records. 
• Object-oriented database: This last type of database uses objects rather than tables, which relational databases use. With object-oriented databases, object-oriented programmers can purposely build the databases they need. 

Database design best practices

When you’re ready to design your database, keep these best practices in mind. 

1. Keep it simple

As you design, think about your users. Put usability at the forefront and ensure that everything is as easy and straightforward as possible for the end-user, even if that means more work for you upfront. 

• Use standardization: Stay consistent with naming conventions and avoid abbreviations. You want to create a standard and stick with it throughout your database. 
• Consider future modifications: The database is a living thing in the sense that it should be modifiable later.
• Keep technical debt in check: Don’t leave too many potential messes for users to workaround or for future devs to resolve. 

Technical debt can cause performance problems and a decline in quality. Think carefully about how you take on technical debt and manage it.Read more

2. Normalize your data 

Keep redundancy to a minimum and protect your data’s consistency. 

• Verify dependencies: Make sure your dependencies are in order and remain consistent throughout your database. 
• Prevent anomalies: Update, insertion, and deletion anomalies can be prevented by double-checking your database dependencies. 

3. Consider the running conditions

Your database won’t stay in beta forever—at some point, users will be putting your database to use. Real-world conditions may not be ideal, and you need to plan ahead for them so your database is up to the challenge. 

• Design for the long term: Look ahead at how your users will need to scale, adapt, or use your database differently from how it’s originally designed. 
• Create documentation: Carefully document, even if it’s a pain to do right now, so your users have an easier time later. 
• Diagram your data: A visual representation can be a helpful way for your users to understand data relationships and structures. 
• Plan for resource limitations: In all likelihood, your database and your app will run alongside others or you’ll be sharing computing resources. Take potential limits into consideration. 

4. Collaborate more—bring the devs and DBAs together 

Many organizations still treat the database like it’s distinct enough that the DBA doesn’t need to work closely with DevOps. But this mindset can create discrepancies.

• Communicate expectations: Other teams should know which actions are only for database administrators (for instance, making database changes). 
• Loop in the DBA: Find ways for the DBA to easily chat with the developers and vice versa. 

5. Model your data and look for the right fit for your needs

Since your database design is so important, take some time with it. Create a model, develop a diagram, and incorporate your team’s input. 

• Minimize unnecessary maintenance: Modeling and thinking about your database can keep your resource investment down and make it easier for you to maintain your database later. 
• Use visualization: Create a diagram and test it. Does your diagram fit your data, and does the structure you chose fit your needs? 
• Test your database: Spend some time in testing and don’t skip this step. A non-functional database is more costly in the long run than an extended deadline or investing the time.