Primary Keys and Normalization

Question

I have to normalize my database up to BCNF but I'm a little bit confused about 1NF.

My main table is about video games and it looks like this:

game_id(PK, AI)    title    developer_id(FK)    publisher_id(FK)    genre                   modes                               release_date  
    1              XYZ          3                   5               FPS, Battle Royale      Single-player, multiplayer          2019-02-04

My confusion is about the "modes" and "genre" columns because sometimes I have more than one value so it's not atomic, right? This comment says the same Explain Like I am Five -> How a Primary Key Satisfies First Normal Form BUT the comment right below him says the opposite...

I've read about normalization and I found this example of a table in 1NF. Looking back at my table, I realized I can't do the same because I have an 'id' which is a PK so it can't have duplicate values. Other sources say that once you have a PK it means that the table is in 1NF because all rows are different.

I'm thinking about creating another two tables where I store only the 'modes' which are (Single-player/Multiplayer/Single-player, multiplayer) and the 'genres'. But, how would I insert the 'mode_id' as an FK in the 'game' table if, again, a game has only one 'id' which is also a PK?

Then I guess it also satisfies NF2? And for NF3 I should create two more tables where I store the n-m relationships between games-modes and games-genres...

I'd be glad if anyone wants to look at my whole database (it's small and simple) and tell me where I should apply normalization. Thank you!

Answer 1

The comment you linked is correct, the comment below that is wrong.

1NF says that each attribute needs to be atomic (which is usually meant semantically, not technically. Technically, the string "FPS, Battle Royale" is as atomic as any other, because technically any string is as atomic as any other.

But 1NF is meant semantically: you should not store multiple values in a single column of a single row.

The easiest way to make your table 1NF is just to transform it into

game_id(PK, AI)    title    developer_id(FK)    publisher_id(FK)    genre          modes                               release_date  
1                  xyz      3                   5                   FPS            Single-player          2019-02-04
1                  xyz      3                   5                   FPS            multiplayer          2019-02-04
1                  xyz      3                   5                   Battle Royale  Single-player          2019-02-04
1                  xyz      3                   5                   Battle Royale  multiplayer          2019-02-04

Of course, this is only 1NF. Fully normalized, it should be something along:

table GAMES
game_id(PK, AI)    title    developer_id(FK)    publisher_id(FK)    release_date  
1                  xyz      3                   5                   2019-02-04

table GENRES
genre_id(PK)       name
1                  FPS
2                  Battle Royale

table MODES
mode_id(PK)        name
1                  Single-player
2                  multiplayer

table GAME_GENRE
id      game_id      genre_id
1       1            1
2       1            2

table GAME_MODE
id      game_id      mode_id
1       1            1
2       1            2

``