Connecting `Book` and `Author` classes

Since we have two separate (model) classes, we need to somehow tie the two together. We can acheive this by either making a reference to the Author class from the Book class or the other way around. Let us go with first one since in real world an author may have a large number of books but a book may not have many authors.

So far, we have had a field named author of type Author in the Book class1. But, these are POJO classes that are going to be mapped to a database. In order to facitilate this mapping, let us replace that with another field named authorId that will be used to reference an author. In particular, authorId in Book will reference the id in class Author. This results in the following Book and Author classes.

In terms of Table Relations

Great! Now that we have our POJO classes in place, we need to map them into our SQLite database. With the current design we have, we need two tables named Books and Authors where there is a many-to-one relationship between Books and Authors tables. The only question is how do we connect these two tables? This leads us to concept of Foreign Key.

Foregin Key

A foreign key is a column or group of columns in a relational database table that provides a link between two tables. A foreign key creates an inclusion dependency constraint, which in simple terms means the values of the foreign key attributes stored in the referencing table2 must exist in the referenced table.3

Now, let us (re)create our Author table:

CREATE TABLE IF NOT EXISTS Authors (id INTEGER PRIMARY KEY, name VARCHAR(100) NOT NULL UNIQUE,
numOfBooks INTEGER, nationality VARCHAR(30));

Next, we create the Books table with a foreign key constraint:

CREATE TABLE IF NOT EXISTS Books (id INTEGER PRIMARY KEY, title VARCHAR(200) NOT NULL,
isbn VARCHAR(14) NOT NULL UNIQUE, publisher VARCHAR(14), year INTEGER,
authorId INTEGER NOT NULL, FOREIGN KEY(authorId) REFERENCES Authors(id)
ON UPDATE CASCADE ON DELETE CASCADE);

Pay attention to the last two lines in particular. FOREIGN KEY(authorId) REFERENCES Author(id) states that authorId attribute in the current table (i.e., Books) references the id column in the Authors table. In (relational) database terminology, the Authors table is called the parent table and Books is the child table.

The last line decides what will happen to a row (or set of rows) in the Books table with a particular foreign key value (i.e., a particular authorId value) if we update or delete that id value in the Authors table. ON UPDATE CASCADE means if a particular id value in the Authors table is updated, the value is updated for all corresponding rows in the Books table too. Similarly, ON DELETE CASCADE means associated books in the Books table will be deleted "on delete" of any particular id value in the Authors table.

Once the tables are created, we are ready to implement create, read, update, and delete (CRUD) operations for each of Author and Book entities.

  1. For simplicity, our assumption here is that a book has only one author.↩
  2. In this context, the referencing table (i.e. the table that contains the foreign key constraint) is usually called the child table and the referenced table is called the parent table.↩
  3. It is out of the scope of this class to go into details of foreign keys and different kinds of relationships among entities (i.e. tables). All you need to know is that a foreign key creates an explicit constraints among two tables such that: 1) all foreign key values stored in the child table must exist in the parent table, and 2) you can control what will happen to the records (i.e., rows) in the child table if we update or remove a referenced value(s) from the parent table.↩
Edit this page