Мастерская IT-решений: “📜 Физическая модель данных 📜 На данном этапе разрабатывается физичес…”

📜 Физическая модель данных 📜
На данном этапе разрабатывается физическая структура базы данных, учитывающая особенности выбранной СУБД. Определяются типы полей, индексы, ограничения целостности, триггеры и другие элементы физической структуры.

Структура

🪧Книги
Обратите внимание на типы данных title ISBN. в чем разница?
Название книги - поле переменной длины. ISBN - фиксированной длины. Если символов меньше 13, они заполняются пробелами

Books (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(255),
  isbn CHAR(13),
  publicationyear YEAR,
  pagescount INT,
  availabilitystatus ENUM('Available', 'Borrowed', ‘Borrowed_here’),
  location VARCHAR(255)
)

🪧 Соединение автора и книги
Таблица состоит полностью из внешних ключей

BookAuthors (
  book_id INT,
  author_id INT,
  FOREIGN KEY (book_id) REFERENCES Books(id),
  FOREIGN KEY (author_id) REFERENCES Authors(id)
)

🪧 Соединение жанра и книги

BookGenres (
  book_id INT NOT NULL,
  genre_id INT NOT NULL,
  PRIMARY KEY(book_id, genre_id),
  FOREIGN KEY (book_id) REFERENCES Books(id),
  FOREIGN KEY (genre_id) REFERENCES Genres(id)
)

🪧 Авторы

Authors (
  id INT PRIMARY KEY AUTO_INCREMENT,
  fullname VARCHAR(255),
  biography TEXT
)

🪧 Жанры книг

Genres (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255)
)

🪧 Отдел хранения книг

Departments (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255)
)

Клиенты

Clients (
  id INT PRIMARY KEY AUTO_INCREMENT,
  firstname VARCHAR(255),
  lastname VARCHAR(255),
  readerticketnumber VARCHAR(255),
  phonenumber VARCHAR(255),
  email VARCHAR(255),
  address TEXT
)

🪧Выдача книг
А вот и составной первичный ключ. У записи не делаем свой идентификатор, а делаем первичный ключ из книги и даты выдачи. Одну книгу нельзя выдать 2 раза в один момент. Однозначная идентификация книги обеспечена

Loans (
  book_id INT,
  issuedate DATE,
  user_id INT,
  returndate DATE,
  actualreturndate DATE NULLABLE,
  loantype ENUM('ReadingRoom', 'HomeLoan'),
  PRIMARY KEY(book_id, issuedate),
  FOREIGN KEY (book_id) REFERENCES Books(id),
  FOREIGN KEY (user_id) REFERENCES Users(id)
)

🪧 Штрафы

Fines (
  id INT PRIMARY KEY AUTO_INCREMENT,
  loan_id INT,
  amount DECIMAL(10,2),
  reason TEXT,
  status ENUM('Paid', 'Unpaid'),
  FOREIGN KEY (loan_id) REFERENCES Loans(id)
)