DevDiary

BooksDB Relational Database

BooksDB Relational Database

I have created a small database to demonstrate some fundamental properties of relational databases.

For this example I have defined the relationship between the "authors" and "books" tables as "one-to-many". I have done this because the books contained in my database one have one author.

The BooksGenres table is an example of how to model a "many-to-many" relationship. In this database, a single book can belong to a number of different genres and a single genre can be associated with a number of different books.

Many-to-many: BooksGenres

Query

This query gets all of the records for the table "BooksGenres".

There are two inner joins, to connect the parent tables, Books and Genres.

Many-to-many: BooksGenres

Results

If the user filters by book, the user can get the different genres that the book belongs to.

If the user filters by genre, the user can get the different books that belong to that genre.

Therefore the many-to-many relationship between books an genres can be modelled like this.

Many-to-many: BooksGenres

Group By, Book Title

This query is the same as the one before except it counts how many times each book title appears in the booksgenres table.

In this example it can be used to show the number of different genres that the book belongs to, in the database.

Many-to-many: BooksGenres

Group By, Book Title

Here are the results that the query generates.

This is useful however it doesn't specify what the genres are.

Many-to-many: BooksGenres - Query (Pivot)

Many-to-many: BooksGenres - Query (Pivot)

This query is an example of a pivot table.

It uses the first query (see above) and lets the user see which Genres that each book belongs to, in one row.

Many-to-many: BooksGenres - Results (Pivot)

Many-to-many: BooksGenres - Results (Pivot)

This query is useful if the information needs to be saved to a spreadsheet or just needs to be presented horizontally rather than vertically.