---
title: "Designing a normalized database schema in SQL Server"  
description: "Designing a normalized database schema in SQL Server involves organizing data into tables and defining relationships between them."  
author: "Ashutosh Patel"  
published: 2024-07-11  
updated: 2024-07-11  
canonical: https://www.mindstick.com/articles/336393/designing-a-normalized-database-schema-in-sql-server  
category: "mssql server"  
tags: ["mssql server", "sql server", "sql"]  
reading_time: 3 minutes  

---

# Designing a normalized database schema in SQL Server

#### SQL Server Design Normalized Database Schema

Creating a generalized [database schema](https://answers.mindstick.com/qa/111801/what-are-the-key-considerations-for-optimizing-database-schema-designs) in SQL Server involves organizing data into tables and defining relationships between them to reduce redundancy and ensure [data integrity](https://www.mindstick.com/forum/160197/what-is-the-normalization-in-sql-server-explain-its-impact-on-data-integrity-and-performance).

Here is a step-by-step process for creating a generalized database schema,

#### Identify Entities

**Identify the main entities-** These are the key features or concepts in your [application domain](https://www.mindstick.com/interview/718/what-is-an-application-domain). For example, in an e-commerce application, companies can add `Customers`, `Products`, `Orders`, and so on.

#### \
Define relationships

**Identify relationships between entities-** Identify how the companies interact with each other. Relationships can be one to one, one to many, or many to many.\
**Use foreign keys-** Define `foreign keys` in tables to establish relationships. For example, an **Order** may have a `CustomerID` that accesses the Customer table.

#### \
Normalize the Schema

**First Normal Form (1NF)-** Ensure that each table has a primary key and all columns have atomic (non-divisible) values. Avoid repeating groups or orders in different columns.

\
**Second Normal Form (2NF)-** Ensure that any non-key columns are completely dependent on the entire primary key. [If necessary](https://www.mindstick.com/forum/23229/in-python-check-if-a-directory-exists-and-create-it-if-necessary), split the tables further to accomplish this.

\
**Third Normal Form (3NF)-** [Remove columns](https://www.mindstick.com/forum/156738/how-to-add-and-remove-columns-from-an-existing-table-in-the-database) that do not depend on the primary key, and move them to separate tables if necessary.

#### \
Design Table Structure

**Define Columns-** For each table, identify the attributes (columns) that describe the object or relationship. Make sure that each column represents one block of data (atomicity).\
**Select appropriate datatype-** Select the appropriate SQL Server data type (e.g., `INT`, `VARCHAR`, `DATE`, `DECIMAL` etc.) based on the data type and expected values.

#### Create Primary and Foreign Keys

**[Primary keys](https://answers.mindstick.com/qa/30985/what-are-primary-keys-and-foreign-keys)-** Define primary keys for each table to uniquely identify each row. This can be a single color or a composite key (multiple colors).\
**Foreign keys-** Use foreign keys to establish relationships between tables. A foreign key refers to the primary key of another table to ensure the integrity of the specification.

## Example-

Here is a simplified example of an e-commerce application,

**Customer** table-

```plaintext
CREATE TABLE Customer (
   CustomerID INT PRIMARY KEY,
   FirstName VARCHAR(50),
   LastName VARCHAR(50),
   Email VARCHAR(100) UNIQUE
   -- Other customer attributes
);
```

**Product** table-

```plaintext
CREATE TABLE Product (
   ProductID INT PRIMARY KEY,
   ProductName VARCHAR(100),
   Price DECIMAL(10, 2),
   -- Other product attributes
);
```

**Order** table-

```plaintext
CREATE TABLE Order (
   OrderID INT PRIMARY KEY,
   CustomerID INT FOREIGN KEY REFERENCES Customer(CustomerID),
   ProductID INT FOREIGN KEY REFERENCES Product(ProductID),
   OrderDate DATE,
   TotalAmount DECIMAL(10, 2),
   -- Other order attributes
);
```

#### Normalize Further (if necessary)

Consider further normalization: If a table exhibits redundancy or nonatomic properties, consider further normalization to higher normal forms (e.g., `4NF` or `5NF`).

\
Normalizing a database structure in SQL Server involves creating accurate structures, defining objects and relationships, regularizing tables, and ensuring [data consistency](https://www.mindstick.com/forum/159960/how-can-you-ensure-data-consistency-in-a-microservices-architecture) through special foreign interfaces This step which you will follow helps to achieve a robust and efficient [database design](https://www.mindstick.com/forum/34620/database-design) that best supports [your application](https://answers.mindstick.com/qa/97584/how-do-you-choose-the-correct-camera-for-your-application)'s needs.

**Also, Read:** [How to use SQL Server indexing to optimize query performance?](https://www.mindstick.com/articles/336392/how-to-use-sql-server-indexing-to-optimize-query-performance)

---

Original Source: https://www.mindstick.com/articles/336393/designing-a-normalized-database-schema-in-sql-server

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.