Video Game Library

Schema Snippet — Core Relations

-- game references developer & publisher
CREATE TABLE game (
    game_id      INT AUTO_INCREMENT PRIMARY KEY,
    title        VARCHAR(150) NOT NULL,
    release_year YEAR,
    developer_id INT,
    publisher_id INT,
    FOREIGN KEY (developer_id) REFERENCES developer(developer_id),
    FOREIGN KEY (publisher_id) REFERENCES publisher(publisher_id)
);

-- M:N — a game can belong to many genres
CREATE TABLE game_genre (
    game_id  INT NOT NULL,
    genre_id INT NOT NULL,
    PRIMARY KEY (game_id, genre_id),
    FOREIGN KEY (game_id)  REFERENCES game(game_id),
    FOREIGN KEY (genre_id) REFERENCES genre(genre_id)
);

-- borrowing tracks who borrowed what and when
CREATE TABLE borrowing (
    borrowing_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id      INT  NOT NULL,
    game_id      INT  NOT NULL,
    borrow_date  DATE NOT NULL,
    return_date  DATE,
    FOREIGN KEY (user_id) REFERENCES user(user_id),
    FOREIGN KEY (game_id) REFERENCES game(game_id)
);