"Player has spaceships" database-CodePudding

I'm pretty new to modeling databases, this is for a browser game.

Basically a player can spend resources to build spaceships. There are, let's say, 3 types of spaceships.

As I understand it's a 1-N relationship, but I'm really confused at how can I save the quantity of each type of spaceship from a specific player.

Right now I have a Player table, a Spaceship table, and Spaceship table contains 3 rows that represents the specific types of spaceships, with their own name, defense etc. Is that ok ?

I know that Spaceship will store Player's id as a foreign key, but I wonder if I just have to use COUNT function to display the quantity for each spaceship, or use an intermediate association like "Player-has-Spaceship" table with quantity attribute. The latter makes more sense to me.

Didn't try to code it blindly, I want a clear concept first.

CodePudding user response：

CREATE TABLE counts (
    player_id ...,
    spaceship_id ...,
    cnt INT UNSIGNED NOT NULL,
    PRIMARY KEY(player_id, spaceship_id)
) ENGINE=InnoDB;

UPDATE counts SET
        cnt = cnt   1;
    WHERE  player_id = ?
      AND  spaceship_id = ?