Catalog My learning Blog
Full-Stack Developer Course: React, .NET and SQL Server · Phase 4 · Lesson 31 · 7 min read

Relational Databases for Frontend Developers

On this page
  1. Learning Objective
  2. Concept Explanation
  3. Worked Example: The Task Board Schema in T-SQL
  4. Real-World Examples
  5. Review Questions & Answers

Learning Objective

Model application data as related tables instead of nested objects, choose primary and foreign keys, apply normalization to remove duplication, use database constraints as a last line of defense, and choose appropriate SQL Server data types.

Concept Explanation

Frontend developers think in objects and arrays: a project object containing an array of task objects, each containing an array of comments. That shape is convenient for rendering, but as a storage model it has problems. If a user's display name is copied into every comment they wrote, changing it means finding and editing thousands of copies, and missing one leaves the data inconsistent. The relational model, described by E. F. Codd in 1970, solves this by storing each fact once, in a table of rows and columns, and connecting facts with keys. If you have used a normalized Redux store, with entities held by id in separate maps and references between them, you already know the idea: the database is a normalized store with guarantees.

A primary key uniquely identifies each row. A foreign key is a column that refers to the primary key of another table, and the database enforces that the referenced row exists. One-to-many relationships, such as a project having many tasks, are modeled by putting the parent's key in the child table. Many-to-many relationships, such as users belonging to many projects and projects having many users, use a join table holding two foreign keys, and often extra columns of their own, such as the member's role. Choosing a key type is a real decision: integers are compact and fast; GUIDs are hard to guess and can be generated by clients or multiple services, but random GUIDs fragment a clustered index, so SQL Server developers often use NEWSEQUENTIALID or generate sequential GUIDs in application code.

Normalization is the discipline of removing redundancy. In first normal form each column holds a single value and there are no repeating groups, so a comma-separated list of tag names in one column is a warning sign. In second and third normal form, every non-key column depends on the whole key and nothing but the key, so a task row should not store the project's name, only the project's id. Normalization makes updates safe and storage small. It costs joins on reads, and deliberate denormalization, such as storing a comment count on a task, is legitimate when measured performance demands it, provided you keep the copy consistent and treat the normalized data as the truth.

Constraints deserve more attention than frontend developers usually give them. NOT NULL, UNIQUE, CHECK, and FOREIGN KEY constraints enforce rules in the database itself, so they hold regardless of which service or script writes the data and regardless of bugs in the application. This is defense in depth: your API validates input, and the database still refuses a negative quantity or an orphaned comment. Choose data types deliberately: nvarchar for Unicode text with an explicit length, datetime2 or datetimeoffset for times, decimal with precision for money, bit for booleans, uniqueidentifier for GUIDs, and rowversion for concurrency tokens. Avoid storing money in float and avoid unbounded nvarchar max for values that have a natural limit.

ConceptFrontend analogyRelational form
EntityAn object type or a map in a normalized storeA table
IdentityThe id fieldPrimary key
Reference between entitiesAn id stored in another objectForeign key with enforced integrity
Array of childrenproject.tasksChild table with the parent's key
Many-to-manyTwo arrays of idsJoin table with two foreign keys
Validation rulesForm validation or Zod schemaConstraints: NOT NULL, UNIQUE, CHECK

Worked Example: The Task Board Schema in T-SQL

The schema below models projects, members, tasks, and comments. Each fact is stored once, relationships use keys, and constraints encode rules. Read the constraint names and comments as the checks the database will enforce even if the API has a bug.

SQL
CREATE TABLE dbo.Users (
    Id            UNIQUEIDENTIFIER NOT NULL CONSTRAINT PK_Users PRIMARY KEY DEFAULT NEWSEQUENTIALID(),
    Email         NVARCHAR(256)    NOT NULL,
    DisplayName   NVARCHAR(80)     NOT NULL,
    CreatedAt     DATETIMEOFFSET   NOT NULL CONSTRAINT DF_Users_CreatedAt DEFAULT SYSUTCDATETIME(),
    CONSTRAINT UQ_Users_Email UNIQUE (Email)
);

CREATE TABLE dbo.Projects (
    Id        UNIQUEIDENTIFIER NOT NULL CONSTRAINT PK_Projects PRIMARY KEY DEFAULT NEWSEQUENTIALID(),
    Name      NVARCHAR(120)    NOT NULL,
    OwnerId   UNIQUEIDENTIFIER NOT NULL CONSTRAINT FK_Projects_Owner REFERENCES dbo.Users(Id),
    RowVer    ROWVERSION       NOT NULL
);

-- Many-to-many between users and projects, with a role attribute of its own.
CREATE TABLE dbo.ProjectMembers (
    ProjectId UNIQUEIDENTIFIER NOT NULL CONSTRAINT FK_PM_Project REFERENCES dbo.Projects(Id) ON DELETE CASCADE,
    UserId    UNIQUEIDENTIFIER NOT NULL CONSTRAINT FK_PM_User    REFERENCES dbo.Users(Id),
    Role      TINYINT          NOT NULL CONSTRAINT CK_PM_Role CHECK (Role IN (1, 2, 3)),  -- 1 viewer, 2 editor, 3 admin
    CONSTRAINT PK_ProjectMembers PRIMARY KEY (ProjectId, UserId)
);

CREATE TABLE dbo.Tasks (
    Id          UNIQUEIDENTIFIER NOT NULL CONSTRAINT PK_Tasks PRIMARY KEY DEFAULT NEWSEQUENTIALID(),
    ProjectId   UNIQUEIDENTIFIER NOT NULL CONSTRAINT FK_Tasks_Project  REFERENCES dbo.Projects(Id) ON DELETE CASCADE,
    AssigneeId  UNIQUEIDENTIFIER NULL     CONSTRAINT FK_Tasks_Assignee REFERENCES dbo.Users(Id),
    Title       NVARCHAR(200)    NOT NULL,
    Status      TINYINT          NOT NULL CONSTRAINT CK_Tasks_Status CHECK (Status BETWEEN 0 AND 3),
    Estimate    DECIMAL(9, 2)    NULL     CONSTRAINT CK_Tasks_Estimate CHECK (Estimate IS NULL OR Estimate >= 0),
    CreatedAt   DATETIMEOFFSET   NOT NULL CONSTRAINT DF_Tasks_CreatedAt DEFAULT SYSUTCDATETIME(),
    RowVer      ROWVERSION       NOT NULL
);
CREATE INDEX IX_Tasks_Project_Status ON dbo.Tasks (ProjectId, Status) INCLUDE (Title);

CREATE TABLE dbo.Comments (
    Id        UNIQUEIDENTIFIER NOT NULL CONSTRAINT PK_Comments PRIMARY KEY DEFAULT NEWSEQUENTIALID(),
    TaskId    UNIQUEIDENTIFIER NOT NULL CONSTRAINT FK_Comments_Task   REFERENCES dbo.Tasks(Id) ON DELETE CASCADE,
    AuthorId  UNIQUEIDENTIFIER NOT NULL CONSTRAINT FK_Comments_Author REFERENCES dbo.Users(Id),
    Body      NVARCHAR(4000)   NOT NULL,
    CreatedAt DATETIMEOFFSET   NOT NULL CONSTRAINT DF_Comments_CreatedAt DEFAULT SYSUTCDATETIME()
);

Notice that the author's display name appears only in Users, so renaming a person changes one row. The CHECK constraints mean a task with a negative estimate or an unknown status cannot be stored even by a script that bypasses the API. And ProjectMembers, not the Projects table, records who may access which project, which is the data authorization checks will rely on in Phase 5.

Users own projects, projects contain tasks and tasks contain comments. A ProjectMembers join table records the many-to-many relationship between users and projects together with the member role.
Users own projects, projects contain tasks and tasks contain comments. A ProjectMembers join table records the many-to-many relationship between users and projects together with the member role.

Real-World Examples

  • Edgar F. Codd, working at IBM, published the paper A Relational Model of Data for Large Shared Data Banks in 1970, proposing that data be organized in relations and manipulated with declarative operations. Every mainstream relational database, including SQL Server, descends from that model, and the normal forms you learned today were defined in his subsequent papers.
  • Microsoft publishes sample databases such as WideWorldImporters and AdventureWorks, which model realistic businesses with normalized tables, foreign keys, constraints, and temporal tables, and are widely used for learning and for testing SQL Server features. Studying a well-designed schema is one of the fastest ways to absorb good relational modeling habits.
Key TakeawaysStore each fact once, connect facts with primary and foreign keys, and use join tables for many-to-many relationships. Normalize by default and denormalize only with measured reasons, enforce rules with database constraints as defense in depth, and choose SQL Server data types deliberately, especially for keys, time, and money.

Review Questions & Answers

The same fact is stored in many places, so changing the name requires updating all copies, and any missed copy leaves the data inconsistent. Storing only the author's id in the comment and the name once in the users table means an update touches one row and every query that joins sees the current value. This is the core benefit of normalization.

It guarantees that a referenced row exists, so a comment cannot point to a task that was deleted or never existed, regardless of which code path writes the data. Application code can have bugs, race conditions, or be bypassed by scripts and other services, while the constraint is enforced by the database on every write.

When measured performance needs, such as an expensive aggregate shown on every list row, justify storing a precomputed copy like a comment count. The risk is that the copy can drift from the source of truth if updates miss it, so it must be maintained transactionally or by a reliable process, and the normalized data must remain authoritative.

SQL Server physically orders a clustered index by the key, so random values insert into arbitrary pages, causing page splits and fragmentation and slowing writes. Sequential keys append to the end. Remedies include NEWSEQUENTIALID defaults, generating sequential GUIDs in application code, or using integer identity keys internally with a separate public identifier.