I'm currently designing my book collection software, because I couldn't find any that suits my particular needs, especially when it comes to translations.Here are my particular needs:Books, authors and publishers must be sortable. This can be derived by the name, but shouldn't.A book can have several authors and an author can write several books.Several authors can have different roles when making a book: a book can have a writer, a translator, an illustrator; a comic book can have a writer, a drawer, a colorer, etc.A book can be published by different publishers and a publisher can edit several books: I have books that were originally published by a publisher but a subsequent version (pocket version, for instance) was published by another publisher. Their ISBN are different.A book can be translated into several languages: I own copies of The Lord of the Rings in English, French, German and Dutch. There must be a link between a translation and the original piece.If a book is a translation, I want to have a reference to the original book, even if I don't own any copies that original version.I18n is done at the application level so I don't need full labels for the language: only code. Same for the roles.This SQL is currently a HSQLDB code (because I'm only starting the development), but it will be translated into PostgreSQL (for a server version of the application) and SQLite (as cache from the server version in a mobile version of the application) in the future.I try to make this database as normalized as possible. My specific questions about this code are the following:Does this SQL make sense in regards to my needs? I couldn't find any use-cases that don't fit.Is my normalization good enough? I have some doubts about not using a language table for my translations, but then it'd have only the id and the code. Also, it would make sense in such language table to actually use the iso 639-1 (or 639-2) as PK.What is more acceptable: using a table translation, or discarding it and placing the book language in the book table?I've put many foreign keys as primary keys. Is this acceptable?Are my cascades going to make me trouble?Are there obvious issues in designing this?Here's the code:CREATE TABLE book (  id          BIGINT IDENTITY,  title       VARCHAR(255),  title_sort  VARCHAR(255),  CONSTRAINT book_pk PRIMARY KEY(id));CREATE TABLE author (  id          BIGINT IDENTITY,  name        VARCHAR(255),  name_sort   VARCHAR(255),  CONSTRAINT author_pk PRIMARY KEY(id));CREATE TABLE role (  id          BIGINT IDENTITY,  code        VARCHAR(255),  CONSTRAINT role_pk PRIMARY KEY(id));CREATE TABLE book_author (  book_id     BIGINT,  author_id   BIGINT,  role_id     BIGINT,  CONSTRAINT book_author_pk    PRIMARY KEY (book_id, author_id, role_id),  CONSTRAINT book_author_book_fk    FOREIGN KEY (book_id) REFERENCES book(id)      ON DELETE CASCADE,  CONSTRAINT book_author_author_fk    FOREIGN KEY (author_id) REFERENCES author(id)      ON DELETE CASCADE,  CONSTRAINT book_author_role_fk    FOREIGN KEY (role_id) REFERENCES role(id)      ON DELETE CASCADE);CREATE TABLE publisher (  id          BIGINT IDENTITY,  name        VARCHAR(255),  name_sort   VARCHAR(255),  CONSTRAINT publisher_pk PRIMARY KEY(id));CREATE TABLE publication (  book_id       BIGINT,  publisher_id  BIGINT,  isbn          VARCHAR(255) NULL,  date          DATE,  CONSTRAINT publication_pk    PRIMARY KEY(book_id, publisher_id),  CONSTRAINT publication_book_fk    FOREIGN KEY (book_id) REFERENCES book(id)      ON DELETE CASCADE,  CONSTRAINT publication_publisher_fk    FOREIGN KEY (publisher_id) REFERENCES publisher(id) -- no cascade);CREATE TABLE translation (  book_id              BIGINT,  language             CHAR(2), -- iso 639-1  translation_book_id  BIGINT,  CONSTRAINT translation_pk    PRIMARY KEY(book_id, language),  CONSTRAINT translation_book_fk    FOREIGN KEY (book_id) REFERENCES book(id), -- no cascade  CONSTRAINT translation_translation_book_fk    FOREIGN KEY (translation_book_id) REFERENCES book(id)      ON DELETE CASCADE,  CONSTRAINT translation_translation_book_uk    UNIQUE (translation_book_id));
Correctness of a multi-language book collection SQL design
sql