DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • NoSQL for Relational Minds
  • Database Keys: A Comprehensive Guide
  • Query-First Approach in Cassandra
  • Seamless Integration: Connecting AWS Lambda to RDS and Writing Data Effortlessly

Trending

  • MySQL to PostgreSQL Database Migration: A Practical Case Study
  • Integration Isn’t a Task — It’s an Architectural Discipline
  • Beyond ChatGPT, AI Reasoning 2.0: Engineering AI Models With Human-Like Reasoning
  • Why High-Performance AI/ML Is Essential in Modern Cybersecurity
  1. DZone
  2. Data Engineering
  3. Databases
  4. Database Design Best Practices

Database Design Best Practices

Want to learn the best practices of designing a database? Read this article to learn about a few!

By 
Biplab Pal user avatar
Biplab Pal
·
Jun. 13, 18 · Opinion
Likes (10)
Comment
Save
Tweet
Share
23.9K Views

Join the DZone community and get the full member experience.

Join For Free

In today's world, along with Relational databases, we need to consider Non-Relational (No-SQL) databases as well before we start designing database. For more than four decades now, SQL (Structured Query Languages) databases have been a primary data storage mechanism. NoSQL databases like MongoDB, Cassandra, Redis, etc. are gaining attraction in recent days, although NoSQL has existed since the 1960s. No matter, the primary goal of SQL or NoSQL is to store data, only they differ in approaches to storing, retrieving, etc. Also, no one can expect users to explicitly control concurrency, integrity, consistency, or data type validity. That’s why SQL pays a lot of attention to transactional guarantees, schemas, and referential integrity. The end user is often interested in aggregated reporting information, not in separate data items. Depending on a project's needs, we can choose to use a SQL or a NoSQL. We should also keep in mind that one is not a replacement for the other, it's just an alternative.

Let's see a few best practices to design a database and what all we need to consider in the database design process.

What Information (Data) Needs to Be Stored?

This step is to identify what data needs to be stored. Here, along with a database expert, we need a domain expert.

This requires skill on the part of the database designer to elicit the needed information from those with the domain knowledge. This is because those with the necessary domain knowledge frequently cannot express clearly what their system requirements for the database are as they are unaccustomed to thinking in terms of the discrete data elements that must be stored.

Why Are We Storing These?  

It's necessary to know why we need these data to be stored. How is somebody going to use these, and who are they?

What Kind of Query Do We Need to Execute? 

How are we going to use this data?

Data Relationship:

Once a designer is aware of the data that is to be stored in the database, they must then determine where the dependency is within the data.

Structuring Data:

Once the relationships and dependencies amongst the various pieces of information have been determined, it is possible to arrange the data into a logical structure, which can then be mapped into the storage objects supported by the database management system.

The above mentioned points are mostly valid for any types of databases, but there are few important steps that we need to consider based on the database types (relational or non-relational).

Relational Database:

  1. Identifying entities: An entity is an object in the system that we want to model and store information about.
  2. Identifying entity attributes: Attributes are the characteristic of an entity.
  3. Identifying attributes types: The data type of attributes like char, date, number, etc.
  4. Identifying relations between entities: Relationship is a situation that exists between two database tables. Relationships allow databases to split and store data in different tables while linking disparate data items.
  5. Assigning keys (Primary, Foreign keys): Key is used for identifying unique rows from a table. It also establishes the relationship among tables.
  6. Identifying constraints needs to be applied to entities or on columns: Constraints enforce roles for data in a table.
  7. Normalization: Normalization is the process of organizing data in a database. It makes database more flexible by eliminating redundancy and inconsistent dependency.

Non-Relational (NoSQL) Database

  • NoSQL data modeling is typically driven by application-specific access patterns, i.e. the types of queries to be supported.
  • This data modeling often requires a deeper understanding of data structures and algorithms.
  • Data duplication and denormalization are two keys aspects while designing NoSql.

Denormalization: This is the process of copying the same data into multiple documents or tables in order to simplify or optimize query processing or to fit the user’s data into a particular data model. It allows us to store data in a query-friendly structure to simplify query processing. Normalized data increases query execution time for query-join, especially in a distributed architecture.

Soft Schema: All NoSQL provide soft schema capabilities in one way or another:

  • Graph Databases and Key-Value Stores typically do not put constraints on values, so values can be of any format.
  • BigTable models support soft schema via a variable set of columns within a column family and a variable number of versions for one cell.
  • Document databases are inherently schema-less, although some of them allow one to validate incoming data using a user-defined schema.

Soft schema allows one to form classes of entities with complex internal structures (nested entities) and to vary the structure of particular entities and helps in minimization of one-to-many relationships by means of nested entities and, consequently, reduction of joins.

Joins: Joins are rarely supported in NoSQL. For NoSQL, joins are often handled at design time as opposed to relational models where joins are handled at query execution time. Query time joins almost always mean a performance penalty, but in many cases, joins can be avoided using Denormalization and Aggregates, i.e. embedding nested entities. There will be cases where joins are inevitable and should be handled by an application.

Database Scalability: This is a concept in database design that emphasizes on the capability of a database to handle growth in the amount of data and users. Database systems scaling can be broadly categorized into two areas: vertical scaling and horizontal scaling.

  • Vertical scaling: Also known as scaling up, it is the process of adding resources such as memory or more powerful CPUs to an existing server.
  • Horizontal scaling: It is the process of adding more hardware to a system. This typically means adding nodes (new servers) to an existing system.

Both of the scalings can be combined, with resources added to existing servers to scale vertically and additional servers added to scale horizontally when required. It is wise to consider the tradeoffs between horizontal and vertical scaling as you consider each approach.

Note: This article only covers a few of the best practices and there are many others that exist. Also, there are many other factors (not covered here) that need to be considered while designing a database. Please feel free to add them in the comment section so that all of us can learn from each other.

Database design Relational database Data (computing)

Opinions expressed by DZone contributors are their own.

Related

  • NoSQL for Relational Minds
  • Database Keys: A Comprehensive Guide
  • Query-First Approach in Cassandra
  • Seamless Integration: Connecting AWS Lambda to RDS and Writing Data Effortlessly

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!