Database Design Concepts with SQL Server Management Studio (SSMS) Part 1

This article is primarily written for beginners. Still, it covers some interesting and often forgotten database designing concepts that are equally attractive for SQL database professionals.

The current part focuses on the database designing concepts and their mapping to SQL Database tables, columns, and relationships. If you understand the backgrounds of databases and tools we are about to use, you will design your first SQL database with confidence.

Prerequisites 

Before we proceed, ensure having the following things:

1. SQL Server 2016/2017/2019 Express/Developer edition is installed on your machine
2. SSMS (SQL Server Management Studio) is installed

Also, you need to have a basic knowledge of databases and the above tools.

It is not a problem if you don’t have the latest SQL Server and SSMS versions. However, it is highly recommended to have the newer versions, if not the most recent ones available. You can get the necessary versions from the resources below:

• Download SQL Server 2017 Developer edition.
• Download SQL Server 2019 (alternatively, to get the latest SQL Server Express/Developer edition).
• Or, Download a free specialized edition of Developer or Express SQL Server.
• Download SSMS (SQL Server Management Studio)

Note that these links are all working fine at the time of writing this article. If Microsoft decides to replace them, please download the newer version available then.

About SQL Database Design

To start designing your SQL database with SQL Server Management Studio (SSMS), you must have some design plan in your mind.

It is not easy without knowing the core concepts of database designing. However, once you get these concepts and their implementation, you naturally start following the design principles. It is common with almost all database developers.

Let us go through some core database design concepts first. It isn’t easy to cover all of them in one article, but we need something to start from.

We understand a typical database in terms of the following things:

1. Entities
2. Attributes
3. Relationships

What is an Entity?

An entity is anything that the business or an individual would like to store in a database. For instance:

1. Customer.
2. Order.
3. Product.

We can say that a Customer is an entity if the business wants to store it in a database structure for transactional, analysis, and reporting purposes. Similarly, an Order placed by the Customer is also an entity if the business wants to see that information. Therefore, this information must be a part of the database.

However, an Order does not make much sense without a Product. A Product offered to the Customer is also an entity.

How is the Entity Mapped to the Database?

From the database perspective, an entity can be mapped to a table. Thus, if a business needs the Customer, Order, and Product entities, the database developer can map these as three tables.

What is an Attribute?

An attribute is a description of an entity. For instance:

1. CustomerName
2. OrderType
3. ProductName

If the Customer is an entity, the name of the customer (CustomerName) is an attribute. This attribute describes our entity (Customer). Similarly, OrderType is an attribute to the Order entity, and ProductName is an attribute of the Product entity.

How is the Attribute Mapped to the Database?

An attribute, such as CustomerName, describes the Customer table and can be mapped to a column in that table.

An Entity with Multiple Attributes

It is fine for an entity to have multiple attributes. Therefore, we can have many columns (attributes) in a table (entity).

Entity Relationships

An entity can be related to another entity through relationships. A table can be related to another table. There are many types of  entities or tabular relationships:

Customer-Order Relationship (one-to-many)

A Customer (entity/table) can be related to an Order (entity/table) for the following reasons:

1. A customer can place one order.
2. A customer can place many orders.

The opposite is also true:

1. Many orders can be placed by one customer.
2. One order can be placed by one customer.

This is an example of a one-to-many relationship: one customer can place many orders, and many orders can be placed by one customer.

Product-Order Relationship (one-to-many)

A Product (entity/table) can be related to an Order (entity/table) in the following says:

1. A product can be assigned to one order.
2. A product can be assigned to many orders.

Similarly:

1. Many orders can be assigned to a product.
2. One order can have one product.

There is a one-to-many relationship between Product and Order.

Customer-Product Relationship (many-to-many)

Now the relationship between customer and product is explained as follows:

1. A customer can buy one product.
2. A customer can buy more than one product.
3. A product can be bought by a customer.
4. A product can be bought by more than one customer.

Many products can be bought by many customers, which means that the Customer and Product relationship is many-to-many.

Have a look at the illustration below:

Student-Instructor Design Scenario

Let’s consider a different database design scenario. You will implement it using SSMS (SQL Server Management Studio) in the other part of this article.

Business requirements

Suppose you need to design a database which stores the following information:

1. Student(s).
2. Instructor(s).
3. Students who have been allocated an instructor.
4. Instructors who are assigned to the students.

Preliminary Analysis

Upon close observation, you will discover something quite interesting about the above requirements. “The students who have allocated an instructor” and “The instructors who have been assigned to the students” are the same requirement.

It can be frequent that two different-looking requirements turn out to be the same in the context of database design.

Identify Entities

The following entities can be straightaway extracted from the requirements:

1. Student
2. Instructor

However, one more entity serves to provide us with information about the instructors allocated to the students.

Let’s recall the first example where we used an Order table – many customers can buy many orders in the Customer-Order relationship. It is similar to our student-instructor tabular relationship – many instructors can be allocated to many students.

Identify Attributes

We can pick useful attributes for the identified entities, according to that customer-order scenario:

1. Student: Student ID, Name.
2. Instructor: Instructor ID, Name.
3. Student-Instructor: Student-Instructor ID, Student ID, Instructor ID.

Identity Relationships:

Identify the entities relationships:

1. Student -> Student-Instructor (one-to-many).
2. Instructor-> Student-Instructor (one-to-many).
3. Student -> Instructor (many-to-many).

Remember that we always use a mid-table to resolve the many-to-many relationship. That’s why we brought the Student-Instructor entity into the plan.

Mapping Entities and Attributes to tables and columns

Now we can map the entities to tables. Thus, we are going to create the following three tables:

1. Student.
2. Instructor.
3. Student-Instructor.

Similarly, the attributes of those entities, when mapped to the columns, will be as follows:

1. Student: StudentId, Name.
2. Instructor: InstructorId, Name.
3. Student-Instructor: StudentInstructorId, StudentId, InstructorId.

Note the illustration below:

Congratulations! You have successfully learned the database design concepts. We are familiar with entities, attributes, and relationships and the steps to map them to tables and columns in the database.

The next articles will walk you through the database designing steps using SSMS (SQL Server Management Studio).

Things to do

Now that you understand the basics of database designing, try the following things to improve your skills further:

1. Try to add another entity called Supplier with the SupplierId and SupplierName attributes. Check if you can correctly identify the following relationships:
   1. Supplier-Order;
   2. Supplier-Customer;
   3. Supplier-Product.
2. Design a database along with identifying entities, attributes, and relationships for a library. Hint: Books get issued to the members, and members borrow books from the library. Member, Book, Issued can be entities.
3. Identify the type of the following tabular relationships for the entities as mentioned above:
   1. Member-Issued;
   2. Book-Issued;
   3. Member-Book;
   4. Book-Member.

Read also

Learn Database Design with SQL Server Management Studio (SSMS) – Part 2