database/init/01_schema.sql - develop - miragecatv/library-api-csharp

database/init/01_schema.sql
155 строк6 KB
Nikita P
Initial BookDB project
22 июл 2026, 21:33
22 июл 2026, 21:331c0f3fe
-- BookDB
-- PostgreSQL: tables, comments and indexes

CREATE TABLE author (
    id BIGSERIAL PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL UNIQUE
);

COMMENT ON TABLE author IS 'Авторы литературных произведений.';
COMMENT ON COLUMN author.id IS 'Уникальный идентификатор автора.';
COMMENT ON COLUMN author.full_name IS 'Полное имя автора.';

CREATE TABLE book (
    id BIGSERIAL PRIMARY KEY,
    title VARCHAR(500) NOT NULL,
    description TEXT,
    original_year SMALLINT,
    CONSTRAINT chk_book_original_year
        CHECK (original_year IS NULL OR original_year BETWEEN 1 AND 2100)
);

COMMENT ON TABLE book IS 'Литературные произведения независимо от конкретного издания.';
COMMENT ON COLUMN book.id IS 'Уникальный идентификатор произведения.';
COMMENT ON COLUMN book.title IS 'Название произведения.';
COMMENT ON COLUMN book.description IS 'Краткое описание произведения.';
COMMENT ON COLUMN book.original_year IS 'Год первой публикации произведения.';

CREATE TABLE book_author (
    book_id BIGINT NOT NULL,
    author_id BIGINT NOT NULL,
    PRIMARY KEY (book_id, author_id),
    CONSTRAINT fk_book_author_book
        FOREIGN KEY (book_id) REFERENCES book (id) ON DELETE CASCADE,
    CONSTRAINT fk_book_author_author
        FOREIGN KEY (author_id) REFERENCES author (id) ON DELETE RESTRICT
);

COMMENT ON TABLE book_author IS 'Связь многие-ко-многим между произведениями и авторами.';
COMMENT ON COLUMN book_author.book_id IS 'Идентификатор произведения.';
COMMENT ON COLUMN book_author.author_id IS 'Идентификатор автора.';

CREATE TABLE publisher (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE
);

COMMENT ON TABLE publisher IS 'Издательства.';
COMMENT ON COLUMN publisher.id IS 'Уникальный идентификатор издательства.';
COMMENT ON COLUMN publisher.name IS 'Название издательства.';

CREATE TABLE series (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE
);

COMMENT ON TABLE series IS 'Книжные серии.';
COMMENT ON COLUMN series.id IS 'Уникальный идентификатор серии.';
COMMENT ON COLUMN series.name IS 'Название серии.';

CREATE TABLE genre (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE
);

COMMENT ON TABLE genre IS 'Жанры литературных произведений.';
COMMENT ON COLUMN genre.id IS 'Уникальный идентификатор жанра.';
COMMENT ON COLUMN genre.name IS 'Название жанра.';

CREATE TABLE book_genre (
    book_id BIGINT NOT NULL,
    genre_id BIGINT NOT NULL,
    PRIMARY KEY (book_id, genre_id),
    CONSTRAINT fk_book_genre_book
        FOREIGN KEY (book_id) REFERENCES book (id) ON DELETE CASCADE,
    CONSTRAINT fk_book_genre_genre
        FOREIGN KEY (genre_id) REFERENCES genre (id) ON DELETE RESTRICT
);

COMMENT ON TABLE book_genre IS 'Связь многие-ко-многим между произведениями и жанрами.';
COMMENT ON COLUMN book_genre.book_id IS 'Идентификатор произведения.';
COMMENT ON COLUMN book_genre.genre_id IS 'Идентификатор жанра.';

CREATE TABLE edition (
    id BIGSERIAL PRIMARY KEY,
    book_id BIGINT NOT NULL,
    publisher_id BIGINT,
    series_id BIGINT,
    isbn VARCHAR(17),
    publication_year SMALLINT,
    page_count INTEGER,
    binding_type VARCHAR(50),
    condition VARCHAR(50),
    reading_status VARCHAR(50),
    notes TEXT,

    CONSTRAINT fk_edition_book
        FOREIGN KEY (book_id) REFERENCES book (id) ON DELETE CASCADE,

    CONSTRAINT fk_edition_publisher
        FOREIGN KEY (publisher_id) REFERENCES publisher (id) ON DELETE SET NULL,

    CONSTRAINT fk_edition_series
        FOREIGN KEY (series_id) REFERENCES series (id) ON DELETE SET NULL,

    CONSTRAINT uq_edition_isbn UNIQUE (isbn),

    CONSTRAINT chk_edition_publication_year
        CHECK (publication_year IS NULL OR publication_year BETWEEN 1 AND 2100),

    CONSTRAINT chk_edition_page_count
        CHECK (page_count IS NULL OR page_count > 0),

    CONSTRAINT chk_edition_binding_type
        CHECK (
            binding_type IS NULL
            OR binding_type IN ('Твёрдый', 'Мягкий', 'Интегральный', 'Иной')
        ),

    CONSTRAINT chk_edition_condition
        CHECK (
            condition IS NULL
            OR condition IN (
                'Новое',
                'Отличное',
                'Хорошее',
                'Удовлетворительное',
                'Плохое'
            )
        ),

    CONSTRAINT chk_edition_reading_status
        CHECK (
            reading_status IS NULL
            OR reading_status IN ('Не читал', 'Читаю', 'Прочитал', 'Отложил')
        )
);

COMMENT ON TABLE edition IS 'Издания произведений, имеющиеся в домашней библиотеке.';
COMMENT ON COLUMN edition.id IS 'Уникальный идентификатор издания.';
COMMENT ON COLUMN edition.book_id IS 'Идентификатор произведения.';
COMMENT ON COLUMN edition.publisher_id IS 'Идентификатор издательства.';
COMMENT ON COLUMN edition.series_id IS 'Идентификатор книжной серии.';
COMMENT ON COLUMN edition.isbn IS 'ISBN конкретного издания.';
COMMENT ON COLUMN edition.publication_year IS 'Год выпуска конкретного издания.';
COMMENT ON COLUMN edition.page_count IS 'Количество страниц.';
COMMENT ON COLUMN edition.binding_type IS 'Тип переплёта.';
COMMENT ON COLUMN edition.condition IS 'Физическое состояние книги.';
COMMENT ON COLUMN edition.reading_status IS 'Статус чтения.';
COMMENT ON COLUMN edition.notes IS 'Дополнительные заметки.';

CREATE INDEX idx_book_title ON book (title);
CREATE INDEX idx_edition_book_id ON edition (book_id);
CREATE INDEX idx_edition_publisher_id ON edition (publisher_id);
CREATE INDEX idx_edition_series_id ON edition (series_id);
CREATE INDEX idx_edition_publication_year ON edition (publication_year);