## Tables: Books Paragraphs Annotations Audios Images ## Schema: Books id INT PRIMARY AUTOINCREAMENT, name VARCHAR(200) NOT NULL, lang VARCHAR(200) NOT NULL, version INT NOT NULL, cover INT NOT NULL, desc TEXT NOT NULL, Paragraphs id INT PRIMARY AUTOINCREAMENT book_id INT NOT NULL, offset INT NOT NULL, content TEXT NOT NULL, type INT NOT NULL, chapter INT NOT NULL, section INT NOT NULL, subsection INT NOT NULL Annotations id INT PRIMARY AUTOINCREAMENT, paragraph_id INT NOT NULL, keyword VARCHAR(200) NOT NULL, desc TEXT Audios id INT PRIMARY AUTOINCREAMENT, book_id INT NOT NULL, chapter_id INT NOT NULL, md5 VARCHAR(40) UNIQUE NOT NULL, Images id INT PRIMARY AUTOINCREAMENT, md5 VARCHAR(40) NOT NULL, paragraph_id INT NOT NULL, ## Example for query all books ~~~ mysqld SELECT a.id, a.name, a.lang, a.version from books a INNER JOIN (select name, lang, max(version) as version from books GROUP by name, lang) b on a.name = b.name AND a.lang = b.lang and a.version = b.version ~~~ ### example: database: id name lang version 1 first CH 1 2 first EN 1 3 second CH 1 4 first CH 2 5 first CH 3 result: id name lang version 2 first EN 1 3 second CH 1 5 first CH 3 ## ER diagram TODO