Database normalization

Barbershop.sql

CREATE TABLE Barbers (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    FullName NVARCHAR(200) NOT NULL CHECK (FullName <> ''),
    Gender NVARCHAR(10) NOT NULL CHECK (Gender IN ('Male','Female')),
    Phone NVARCHAR(20) NOT NULL UNIQUE,
    Email NVARCHAR(100) NOT NULL UNIQUE,
    BirthDate DATE NOT NULL,
    HireDate DATE NOT NULL,
    Position NVARCHAR(20) NOT NULL CHECK (Position IN ('Chief','Senior','Junior'))
);

CREATE TABLE Services (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL UNIQUE,
    Price MONEY NOT NULL CHECK (Price > 0),
    DurationMinutes INT NOT NULL CHECK (DurationMinutes > 0)
);

CREATE TABLE BarberServices (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    BarberId INT NOT NULL FOREIGN KEY REFERENCES Barbers(Id),
    ServiceId INT NOT NULL FOREIGN KEY REFERENCES Services(Id)
);

CREATE TABLE Clients (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    FullName NVARCHAR(200) NOT NULL CHECK (FullName <> ''),
    Phone NVARCHAR(20) NOT NULL UNIQUE,
    Email NVARCHAR(100) UNIQUE
);

CREATE TABLE BarberFeedbacks (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    BarberId INT NOT NULL FOREIGN KEY REFERENCES Barbers(Id),
    ClientId INT NOT NULL FOREIGN KEY REFERENCES Clients(Id),
    Feedback NVARCHAR(MAX),
    Rating INT NOT NULL CHECK (Rating BETWEEN 1 AND 5) -- 1=дуже погано, 5=чудово
);

CREATE TABLE BarberSchedule (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    BarberId INT NOT NULL FOREIGN KEY REFERENCES Barbers(Id),
    Date DATE NOT NULL,
    TimeSlot NVARCHAR(20) NOT NULL, -- наприклад "10:00-11:00"
    IsBooked BIT NOT NULL DEFAULT 0
);

CREATE TABLE Appointments (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    BarberId INT NOT NULL FOREIGN KEY REFERENCES Barbers(Id),
    ClientId INT NOT NULL FOREIGN KEY REFERENCES Clients(Id),
    ServiceId INT NOT NULL FOREIGN KEY REFERENCES Services(Id),
    Date DATE NOT NULL,
    TimeSlot NVARCHAR(20) NOT NULL,
    TotalPrice MONEY NOT NULL CHECK (TotalPrice > 0),
    Feedback NVARCHAR(MAX),
    Rating INT CHECK (Rating BETWEEN 1 AND 5)
);

CREATE TABLE VisitArchive (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    AppointmentId INT NOT NULL FOREIGN KEY REFERENCES Appointments(Id),
    BarberId INT NOT NULL,
    ClientId INT NOT NULL,
    ServiceId INT NOT NULL,
    Date DATE NOT NULL,
    TotalPrice MONEY NOT NULL,
    Feedback NVARCHAR(MAX),
    Rating INT CHECK (Rating BETWEEN 1 AND 5)
);