Relational Databases

A big part of any application is the relational database. Simply speaking, adding, deleting, or moving things around in a database is the primary function of most apps.  

Databases are a lot like a collection of spreadsheets. If you have ever worked with Excel, then database tables will seems quite similar. The difference is that the columns in a database table are very specific. They are defined for one kind of data and enforce those rules religiously. Another major difference with databases is that databases manage relational data, meaning that data from one table often references data from another table. This simple idea enables a three dimensional data structure which is very powerful. We will explore all of these concepts in more detail.

If you are planning on building data-driven applications with ASP.NET, check out the Nano ASP.NET Boilerplate. Its a .NET power tool for creating scalable applications quickly, taking care of all the architecture and infrastructure, with detailed documentation.

Popular Database Technologies

There are several popular database technologies in existence. The one we will use is called SQL Server, and it is provided by Microsoft. Many versions of SQL Server are free, including the Express edition, which is great for development and small projects. SQL Server is widely used when building applications with C# or .NET.

That said, all databases are conceptually similar. They use tables, rows, columns, keys, and SQL to organize and retrieve data. Unless you’re dealing with very specific requirements like licensing restrictions, performance tuning at extreme scale, or specialized hosting environments, any major relational database system will work just fine for most use cases.

Here are a few of the big names in relational databases:

• SQL Server
• Oracle
• MySQL
• PostgreSQL
• MariaDB

Since we’re working in the Microsoft ecosystem, SQL Server will give us the smoothest experience — especially when combined with tools like Entity Framework and LINQ.

SQL (Structured Query Language)

Each of these database systems supports the Structured Query Language (SQL) — a language designed to communicate with and manage relational databases. Whether you’re retrieving information, inserting new data, or updating existing records, SQL is the tool you’ll use to tell the database what to do.

SQL is a command language, not a real programming language. SQL commands are sent to a database and the database engine carries out the actions. Typically this interaction is done programmatically through code, or via a terminal in a database explorer tool like SQL Server Management Studio. The Visual Studio IDE also has a database explorer tool built in.

There are many SQL commands like select, insert into, update, delete, and, or not, etc. Here is an example of an SQL command that would create a new database table with four columns, each with a specific data type:

CREATE TABLE Customers (
    Id INT PRIMARY KEY,
    Name NVARCHAR(100),
    Email NVARCHAR(100),
    CreatedDate DATETIME
);

Here is another command that would retrieve all the data rows from a particular table.

Select * FROM Customers;

It takes time to practice and master SQL commands. If that sounds fun to you, there are some great resources like W3Schools that have all the examples. This course however is a practical course, not an academic one so we won’t go into much detail on using SQL. The reality is that you don’t need to know much about SQL because in ASP.NET we have tools that handle the SQL language for us like Entity Framework and LINQ. It never hurts to know some SQL though.

Relational Database Tables

What makes relational databases powerful is how you can connect data across multiple tables. For example, a customer might place many orders, and each order might contain several products. Instead of repeating all the customer or product details, we store those once and use keys to relate the data.

In a relational database, usually the first column in every table is the primary key column. This column is typically an integer type and is named ID. The primary key values are usually set automatically by the database engine and are always unique. For example, the first row in a Customers table would have an ID of 1. The second row would have a value of 2 for the ID, and so on. Other data types could be used for the ID column like GUIDs (Globally Unique Identifier), or even strings, but they must be unique.

Now imagine another table called Orders. Like all tables, this one also has an ID column with unique values for each row as well as columns for order details like Order Date, Description, Status, and a reference column called CustomerID. This reference column is special and what’s known as a foreign key. This foreign key column allows the database to form a connection between two tables. They create links between tables, helping maintain data integrity and enabling you to ask complex questions like: “Show me all orders placed by a customer named Alice.”

When we retrieve data about a customer for example, we can easily retrieve related data like all of their orders. On the flipside, if the information about a customer ever changes, it only changes in the Customer table. In a spreadsheet, the data about the customer would be repeated in each row of the orders. This is why databases are much more efficient.

There are several types of relationships we can set up. The previous example of a Customer with Orders was an example of a one-to-many relationship. A similar relationship, yet far less common is a one-to-one relationship. The other common relationship is a many-to-many relationship.

A many-to-many relationship is more complex and requires the use of a join table. An example of a many to many relationship might be Categories and Orders. Consider a scenario where each order is could be tagged with one or several kinds of sale categories like Black Friday, Christmas, Express, Wholesale, Retail, etc. The problem is that we can’t store multiple foreign key IDs in one column.

The solution is to use a join table (sometimes called an associative entity). In this example, the convention would be to name the table OrderCategories. This table would contain two foreign key columns, one for OrderID and one for CategoryID. Now the database can trace a connection to the multiple categories per each order by looking up the OrderID column and retrieving each Category by the CategoryID. A primary key for the join table is optional.

With these simple relational building blocks, databases are suddenly extremely powerful. When you buy a McChicken at McDonalds, the NSA can see that your used your credit card to purchase that food, which is connected to your name, which is connected to your medical history, which is connected to your… well you get the idea.

Normalizing Data

You should always normalize data. Data normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. When a database is properly normalized, each piece of information is stored in only one place, which makes the data easier to manage, update, and protect from inconsistencies. Instead of repeating the same data across multiple rows or tables, normalization breaks information into smaller, related tables and uses keys to connect them.

For example, instead of storing a customer’s full details with every order they place, you store the customer’s information in a Customers table and reference their CustomerId in the Orders table. This not only saves space, but also ensures that if the customer’s information changes (like an updated email address), you only have to update it in one place. Normalization typically follows a series of steps called “normal forms,” with each form applying stricter rules for reducing duplication and dependency. While over-normalizing can sometimes lead to complex queries, it’s an essential practice for building clean, scalable, and reliable databases.

Interacting with Databases in C# (LINQ and ORMs)

While writing raw SQL is powerful, it can be tedious and error-prone, especially when you’re building large applications. Fortunately, in C# and ASP.NET, you don’t usually write SQL directly. Instead, you use tools that help abstract the SQL commands and let you work with your data using regular C# code. The most popular tool for this is Entity Framework, a type of Object-Relational Mapper (ORM).

An ORM like Entity Framework lets you map your database tables to C# classes, and your table rows to C# objects. That means instead of writing a SELECT statement to get a list of customers, you can just use C# code like:

var customers = context.Customers.ToList();

This code tells Entity Framework to generate the correct SQL for you, send it to the database, and convert the result into a list of customer objects. Under the hood, the ORM is managing the database connection, executing the query, and handling the results — but you don’t have to worry about any of that.

Entity Framework also supports LINQ (Language Integrated Query), which lets you write query-like expressions directly in C#. LINQ provides a powerful, readable way to filter, sort, and project your data. Here’s an example of a LINQ query to get all active customers:

var activeCustomers = context.Customers
    .Where(c => c.IsActive)
    .OrderBy(c => c.Name)
    .ToList();

This replaces writing the following SQL, and it’s fully type-checked by the compiler — which helps catch errors before your app runs.

SELECT * FROM Customers WHERE IsActive = 1 ORDER BY Name

An ORM like Entity Framework does more than just making queries easier. It takes care of the entire database design and deployment.

When building an application with ASP.NET and Entity Framework, you won’t create a database manually. Instead you will define the tables within a collection of C# classes. This collection functions like a table of contents or a map of the database and is known as a Database Context. The classes form a blueprint for the tables in the database to be created. Entity Framework handles the rest. Defining a string property in one of these classes for example will create a nvarchar column in a database table, and int property will create an integer column.

This concept is called code-first design and solves a lot of problems with managing databases with an application, like source control. In the following lessons where we build an ASP.NET web application, we’ll see code-first design in practice.

Installing SQL Server

In the next lessons we will develop a web application that uses a database for persistent storage. The database we will use is SQL Server. Download and install SQL Server Express, which is the free edition made specifically for development.

Installing SQL Server installs the database as a service which runs in the background of your computer. A database isn’t a program, so after you install it, you won’t notice anything new. It also takes almost zero resources. Its recommended to also get SQL Server Management Studio (SSMS) which is a database management tool which can connect to any SQL Server instance. With this tool you can view tables, make edits, export data, and a lot more. Download it for free.

Conclusion

Databases are at the core of every application. They are responsible for storing all the persistent data and function very much like spreadsheets. Many databases technologies exist, but for the rest of this course we will work with SQL Server.

All databases use SQL as the language for interaction but using tools like Entity Framework and LINQ makes working with databases in C# much more intuitive and maintainable. As we build our ASP.NET applications, you’ll see how these tools allow you to focus on your application logic without having to write raw SQL for every database operation.