CODEWITHSUNDEEP
mysqlsql
user3051755
user3051755

Reputation: 1557

One table or more tables for entity with around 70 attributes

I have a entity with around 70 attributes. It is about a battle in a game. Attacker have ships, while defender have ships and defense units. There are 10 different types of ships and 10 different types of defense units. There can be 0 or more ships and defense units of the same type. There are also 2 types of resources.

So I need to have attribute for every type of attacker ship before and after battle, every type of defender ship before and after battle, every type of defender defense unit before and after battle and every type of resource before and after battle (total 64 attributes) + attributes like id_battle (AUTO_INCREMENT), date...

Are there any problems with performance if table have 70 columns? Is there a better way to create this, maybe with more tables?

Upvotes: 0

Views: 48

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94913

You would normalize your data. There will never be a column shiptype_a and another shiptype_b, but a table holding ship types and maybe others referencing its records in rows, not columns.

More or less:

"There are 10 different types of ships"

  • this suggests a table ship_types

"and 10 different types of defense units."

  • this suggests a table defense_unit_types

"There can be 0 or more ships and defense units of the same type."

  • this suggests either two additional tables: ships and defense_units, or simply going on with the types alone. This depends on whether you want to treat the instances of one type equally or not (do you for instance want to know that it is the ship of type A that was east in the beginning and is now positioned west, or do you simply want to know there is a ship type A west now).

"There are also 2 types of resources."

  • this can be a table or a flag

"Attacker have ships, while defender have ships and defense Units [...] So I need to have attribute for every type of attacker ship before and after battle [...]"

  • So there is one attacker and one defender per game. And it seems you want to store all game steps (so you can later rollback to a previous situation maybe?).

That would lead to the following tables:

  • game (game_id, ...)
  • attacker_ship (game_id, step_no, ship_id)
  • defender_ship (game_id, step_no, ship_id)
  • defender_defense_unit (game_id, step_no, defense_unit_id)

Or in the absence of ship and defense_unit_table:

  • game (game_id, ...)
  • attacker_ship (game_id, step_no, ship_type_id)
  • defender_ship (game_id, step_no, ship_type_id)
  • defender_defense_unit (game_id, step_no, defense_unit_type_id)

If it is a board game with 64 fields like chess, then you can store the whole board, but you would usually still simply store the single fields rather than a record with 64 x n fields (fields1_is_what, field1_attacker_ship_id, field1_defender_ship_id, field1_defender_defense_unit_id, field2_ ...). This has little to do with performance, however, but more with the handling of the database and how to write queries.

Upvotes: 2

Related Questions