Author: Michael Müller

Within this blog, and especially within my book “Practical JSF in Java EE 8” [1] I described the Books project. Some listings show the structure of the tables which are used by this app. But I did not describe all every table. In my poor opinion I assumed, it would be very simple to create the tables with all the info of the entity classes.

Of course, you may derive all table and column names from the entities. But sometimes Java types slightly differ from SQL types and without sufficient experience it might be hard to figure out the right types.

You may modify the persistence.xml file to enable automatic creation of the tables:

<properties>
  <property name="javax.persistence.schema-generation.database.action" 
  value="create"/>
</properties>

Personally I prefer creating the tables by SQL. Here are the create statements:

CREATE TABLE Category (
catId int(11) NOT NULL AUTO_INCREMENT,
catName varchar(45) NOT NULL,
PRIMARY KEY (catId)
)

CREATE TABLE CategoryTranslation (
ctId int(11) NOT NULL AUTO_INCREMENT,
ctCategoryId int(11) NOT NULL,
ctLanguage varchar(10) NOT NULL,
ctName varchar(45) NOT NULL,
PRIMARY KEY (ctId),
KEY FK_listCategoryTrans_listCategory2 (ctCategoryId),
CONSTRAINT FK_listCategoryTrans_listCategory2 
FOREIGN KEY (ctCategoryId) REFERENCES Category (catId) 
)

CREATE TABLE Book (
bookId int(11) NOT NULL AUTO_INCREMENT,
bookTitle varchar(200) NOT NULL,
bookSubtitle varchar(200) NOT NULL,
bookAuthor varchar(255) NOT NULL,
bookPublisher varchar(45) NOT NULL,
bookYear int(11) NOT NULL,
bookLanguage varchar(10) NOT NULL,
bookISBN varchar(45) NOT NULL,
bookShorttext varchar(500) NOT NULL,
bookReference varchar(500) NOT NULL,
bookAdReference varchar(50) NOT NULL DEFAULT '',
PRIMARY KEY (bookId),
UNIQUE KEY IX_Book (bookISBN)
)

CREATE TABLE BookTrans (
btId int(11) NOT NULL AUTO_INCREMENT,
btBookId int(11) DEFAULT NULL,
btLanguage varchar(10) NOT NULL,
btShorttext varchar(500) NOT NULL,
btReference varchar(50) DEFAULT NULL,
PRIMARY KEY (btId),
KEY FK_BookTrans_Book2 (btBookId),
CONSTRAINT FK_BookTrans_Book2 
FOREIGN KEY (btBookId) REFERENCES Book (bookId) 
)

CREATE TABLE mapBookCategory (
bcBookId int(11) NOT NULL,
bcCategoryId int(11) NOT NULL,
PRIMARY KEY (bcBookId,bcCategoryId),
KEY FK_mapBookCategory_listCategory2 (bcCategoryId),
CONSTRAINT FK_mapBookCategory_Book2 FOREIGN KEY (bcBookId) REFERENCES Book (bookId) ,
CONSTRAINT FK_mapBookCategory_listCategory2 
FOREIGN KEY (bcCategoryId) REFERENCES Category (catId) 
)

CREATE TABLE Review (
rvId int(11) NOT NULL AUTO_INCREMENT,
rvBookId int(11) NOT NULL,
rvDate date DEFAULT NULL,
rvLanguage varchar(10) NOT NULL,
rvText longtext NOT NULL,
PRIMARY KEY (rvId),
KEY FK_BookReview_Book (rvBookId),
CONSTRAINT FK_BookReview_Book FOREIGN KEY (rvBookId) REFERENCES Book (bookId) 
)

CREATE TABLE ReviewLink (
Id int(11) NOT NULL AUTO_INCREMENT,
BookId int(11) DEFAULT NULL,
LanguageCode varchar(5) DEFAULT NULL,
URL varchar(255) DEFAULT NULL,
PRIMARY KEY (Id),
KEY FK_ReviewLink_Book (BookId),
CONSTRAINT FK_ReviewLink_Book FOREIGN KEY (BookId) REFERENCES Book (bookId) 
)

Remember, that database provider may use slightly different SQL dialects or data types. The statements above are fine with MariaDB or MySQL. Using a different DBMS you might adapt the statements a little.

I like to thank all of my readers for purchasing my book.

Stay tuned!

[1] www.apress.com/de/book/9781484230299