May 23 2004

Results of FTOS Round 1

Suppose you have a table in your database filled with data about books: title, author, and so on. Every book also has a field for its genre; in that field you put the genre_id corresponding to the genre-table. That’s the easy part. Now, what is the best way to make it possible for every book to have multiple genres?

While this question is not new material for me, I do get this type of question often, so I thought I’d explain my methods.

Problem

Suppose you have a table in your database filled with data about books: title, author, and so on. Every book also has a field for its genre; in that field you put the genre_id corresponding to the genre-table. That’s the easy part. Now, what is the best way to make it possible for every book to have multiple genres?

Solution

Create the following tables in your database. I’m using MySQL syntax here, but I think you can pick out the basics to translate it into any database. I also included only the relevant fields. Much more detail could be put into these tables.

CREATE TABLE `books` (
  `books_id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`books_id`)
) TYPE=MyISAM


CREATE TABLE `genres` (
  `genres_id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`genres_id`)
) TYPE=MyISAM

Now here’s the key to the connection. Rather than create a field in the books table for the genre, we will create an entirely new table relating booksid to genresid:

CREATE TABLE </span><span class="k">books_to_genres</span><span class="dl"> (
  </span><span class="k">books_id</span><span class="dl"> int(11) NOT NULL default '0',
  </span><span class="k">genres_id</span><span class="dl"> int(11) NOT NULL default '0',
  PRIMARY KEY  (</span><span class="k">books_id</span><span class="dl">,</span><span class="k">genres_id</span><span class="dl">)
) TYPE=MyISAM

This is a great example of why databases are a much more powerful and useful tool than spreadsheets. You won’t be able to see the data related visually, but that’s not the point of a database. The information is related via keys, which makes it very easy to get your selected query. Let me explain how I would input and query the following information.

Let’s fill the books table with a few examples:

booksid bookstitle
  1 Designing with Web Standards
  2 Defensive Design
  3 Web Standards Solutions

And sprinkle in a genre or two:

genresid genrestitle
  1 Web Design
  2 Web Standards

Now here’s how we relate the genres. All three entries in the books table fit in the genre of Web Design, but more specifically, Designing with Web Standards and Web Standards Solutions fit into, obviously, the Web Standards genre as well. Here’s how our bookstogenres table would look under these relationships:

booksid genresid
  1 1
  2 1
  3 1
  1 2
  3 2

This specifies a releationship between booksid 1 and genresid 1 and 2, and so on. Then, to list all books in the ‘Web Standards’ genre, we run the following query:

select b.booksid, b.bookstitle from books b, bookstogenres bg where b.booksid = bg.booksid and bg.genresid = '2';

select g.genrestitle from genres g, bookstogenres bg where g.genresid = bg.genresid and bg.books_id = '1';

So there’s your ticket to unlimited books, unlimited genres, and unlimited relationships with three simple, no fuss tables.