CODEWITHSUNDEEP
mysqldatabase-design
saadlulu
saadlulu

Reputation: 1395

One to many database design problem

I am trying to create a database table for my game, and would like to see what is the best approach.

I have lots of monsters, each monster has number of attacks, monsters do not necessarily have the same attacks.

Each monster has different hp, mp and other stats.

Each monster attack has power and speed and other stats.

Can anyone please help me design this table?

I am using MySQL for my database.

Upvotes: 0

Views: 223

Answers (2)

Pete855217
Pete855217

Reputation: 1602

Tables required: MONSTER, MONSTER_ATTACKS.

DROP DATABASE IF EXISTS MONSTER_GAME;
CREATE DATABASE MONSTER_GAME;
USE MONSTER_GAME;
CREATE TABLE MONSTER (
      id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
      hp VARCHAR(64),
      mp VARCHAR(64),
      stats1 VARCHAR(64)
    ) TYPE=innodb;

CREATE TABLE MONSTER_ATTACKS (
      id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
      monster_id INT,
      power INT,
      speed INT,
      Foreign Key (monster_id) references MONSTER(id)
    ) TYPE=innodb;

The important parts: MONSTER has a primary key of id, MONSTER_ATTACKS has a foreign key of monster_id that refers back to MONSTER's id.

Upvotes: 2

Edoardo Pirovano
Edoardo Pirovano

Reputation: 8334

I would recommend having one table for the monsters, for example:

monsters
----------
monster_id
monster_hp
monster_mp

Then have an other table with the monster attacks, which is linked to the previous table by monster_id:

attacks
----------
attack_id
attack_monster #This field links to the monster_id field of other table
attack_power
attack_speed

Of course, you'll probably have more fields in there, but you get the idea.

Upvotes: 0

Related Questions