CODEWITHSUNDEEP
databasepostgresql
hle
hle

Reputation: 19

SQL: How to design the database when one column has multiple categories?

I'm trying to set up a database for a comic website. I'm planning to have:

However, each comic can have multiple genres at the same time (romance, adventure, etc.).

So if I set up the database as above, I'll have to create multiple rows for 1 comic in Table1 which have the same other info: 

(id, title,...)

but just have different genre_id.

This seems wrong...is there a better way to do it?

Upvotes: 0

Views: 620

Answers (2)

alex067
alex067

Reputation: 3301

You can create a many to many relationship table called genres.

This genres table stores all the genres that a comic book is a part of.

A comic book can be a part of multiple genres, and a genre can be a part of multiple comic books. This way, you can keep your comic book table normalized, without creating duplicates.

Genre_mapping table example:

comic_id genre_id 
1           1   
1           2
2           2
3           1
3           5
3           5

Genre table:

genre_id genre_name
1           Comedy
2           Action
3           Romance
4           Adventure
5           Thriller

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270653

You want an association or junction table:

create table comic_genres (
    comic_genres id generated always as identity primary key,
    comic_id int references comics(comic_id),
    genre_id int references genres(genre_id)
);

You can include additional information in the table -- such as the date each row was created.

Upvotes: 0

Related Questions