Making sense of database table references with foreign and primary keys

I am relatively new to database logic, and am trying to figure out how multiple tables should/shouldn’t reference each other.

I have a table, ‘Book’, that should have columns:

| 'title' | 'genre' | 'author' | 'buyOption' | 'pubDate'

1. I want each book to have the possibility to have 1 or more genres.
2. I want each book to have the possibility to have 1 or more authors.
3. I want each book to have the possibility to have 1 or more purchase options (‘buyOption’).
   • and each purchase option (Amazon, Walmart, etc.) for each book has a unique url.

What I think makes sense (please correct me where I’m wrong):

   __________________________
   |                        |
   |           Book         |
   |________________________|
   |Primary Key | book_id   | //seems redundant (same as title_id)...would like to just use title_id, but idk if that's possible
   |------------|-----------|
   |Foreign Key | title_id  | <--------------------------------------------|
   |Foreign Key | bo_id     | <----------------------------------|         |
   |Foreign Key | genre_id  | <--------------------------|       |         |
   |Foreign Key | author_id | <-------------------|      |       |         |
   | - - - - -  | - - - - - |                     |      |       |         |
   |            | pubDate   | //publish date      |      |       |         |
   |________________________|                     |      |       |         |
                                                  |      |       |         |
                                                  |      |       |         |
                                                  |      |       |         |
      __________________________                  |      |       |         |
      |                        |                  |      |       |         |
      |         Authors        |                  |      |       |         |
      |________________________|                  |      |       |         |
      |Primary Key | author_id |------------------|      |       |         |
      |------------|-----------|                         |       |         |    
 |--->|Foreign Key | title_id  |                         |       |         |
 |    | - - - - -  | - - - - - |                         |       |         |
 |    |            |  author   |                         |       |         |
 |    |____________|___________|                         |       |         |
 |                                                       |       |         |
 |                                                       |       |         |
 |    __________________________                         |       |         |
 |    |                        |                         |       |         |
 |    |         Genres         |                         |       |         |
 |    |________________________|                         |       |         |
 |    |Primary Key |  genre_id |-------------------------|       |         |
 |    |------------|-----------|                                 |         |
 |--->|Foreign Key |  title_id |                                 |         |
 |    | - - - - -  | - - - - - |                                 |         |
 |    |            |   genre   |                                 |         |
 |    |____________|___________|                                 |         |
 |                                                               |         |
 |    __________________________                                 |         |
 |    |                        |                                 |         |
 |    |       Buy Options      |                                 |         |
 |    |________________________|                                 |         |
 |    |Primary Key |  bo_id    |---------------------------------|         |
 |    |------------|-----------|                                           |
 |--->|Foreign Key | title_id  |                                           |
 |    | - - - - -  | - - - - - |                                           |
 |    |            | buyBrand  |   //(Walmart, Amazon, etc.)               |                   
 |    |            | buyUrl    |   //(ex: https://www.amzn.com/buyBook1)   |
 |    |____________|___________|                                           |
 |                                                                         |
 |                                                                         |
 |                                                                         |
 |         __________________________                                      |
 |         |                        |                                      |
 |         |          Title         |                                      |   
 |         |________________________|                                      |
 |---------|Primary Key | title_id  |--------------------------------------|
           |------------|-----------|                                           
           |            |   title   |
           |____________|___________|

Does it make sense to have the title table? If so, can i use its primary key to fill various other tables, as depicted?

If the ‘Buy Options’ table is going to have a bunch of different options and associated urls for each book, will it be possible to get the buyBrand and buyUrl directly from the main ‘Book’ table? In the end, I just want a giant table that I can grab cell data from. Right now I’m trying to figure out how to populate tables with my data, and what tables to fill for each piece of data.

(again, I’m new to database logic, so I apologize if my wording is hard to understand)

Answer

Your design does not look good. You are describing many-to-many relationships between books and genres, books and authors, books and options.

Storing references to the related genre, author, and option in the books table is not the right way to go: you can only store one related value per book (one genre, one author, one option), while you need many. Instead, for each of these relationships, you should have a separate table, called a bridge table, that references the associations.

On the other hand, information that is dependent on the book (say, the title) should be stored in the book table.

Here is one example for books and genres:

create table books(
    book_id int primary key,
    title varchar(100),  --dependent column
    pub_date date        --dependent column
);

create table genres(
    genre_id int primary key,
    name varchar(100)
);

create table book_genres(
    book_id int references book(book_id),
    genre_id int references genre(genre_id),
    primary key (book_id, genre_id)
);

Now, say you want to list all books that belong to genre 'Sci-Fi'; you would go:

select b.*
from books b
inner join book_genres bg on bg.book_id = b.book_id
inner join genres g on g.genre_id = bg.genre_id
where g.name = 'Sci-Fi'

The same logic should be implemented for each and every many-to-many relationship in your schema.