What is the best method for storing data in MySQLi with lots of columns?

Question

I have a game that stores boss kills on easy and hard modes in a MySQL database. There are 14 bosses. I want to store the number of times a player has killed a boss and at what difficulty. I have a few options that I can see..

Option 1

Create an individual column in the table for each boss as well as for each difficulty. E.g.

playerid,(all the other player fields here),boss1easy,boss1hard,boss2easy,boss2hard,boss3easy,boss3hard,boss4easy,boss4hard,boss5easy,boss5hard,boss6easy,boss6hard,boss7easy,boss7hard,boss7easy,boss7hard,boss8easy,boss8hard,boss9easy,boss9hard,boss10easy,boss10hard,boss11easy,boss11hard,boss12easy,boss12hard,boss13easy,boss13hard,boss14easy,boss14hard

Option 2

Create 2 fields that store a sequence of numbers that can be extracted later to compare in PHP. Where easykills is a string of 28 characters with every 2 characters a counter for the number of times a boss is killed. E.g. Obviously limit to counting up to 99 kills:

playerid,(all the other player fields),easykills,hardkills
easykills = "2103030405060708091011121314" = boss1easy = 21 kills, boss2easy = 03 kills, boss3easy = 03 kills, boss4esay = 04 kills, etc

Option 3

Create a seperate table called bossKills and simply add 1 to the existing value when a boss is killed. E.g.

playerid,boss,difficulty

My question is this: Which of these methods would result in less load of the server when running MySQLi queries from PHP? Is there a better method that I am unaware of?

Answer 1

The third option is the best one by far.

The second one is something you should always avoid, you can read this answer for more information, but basically, you have to look for database normalization, and that approach is not normalized:

First normal form (1NF) is a property of a relation in a relational database. A relation is in first normal form if the domain of each attribute contains only atomic values, and the value of each attribute contains only a single value from that domain.

If you do that you will have these problems, among other:

• You cannot use JOIN
• You cannot use indexes
• Searching, filtering, sorting, is not easy
• The ability of making references is lost

The first option will be very hard to maintain if you change or extend the logic of the game, like adding bosses or levels of difficulty, since it will force you to change the database schema. It could be a problem in a near future.

so i will use for sure the third option.

Answer 2

The first option is not good as you cannot easily add difficulties or bosses. You will need to change the database schema everytime that you want to add a new boss or difficulty

The second option is not good as you will not be able to run easily sql statements and find for example which player has the most kills on boss 3 at easy difficulty. In order to do something like that, you will have to parse all the strings and extract the info you want from them.

The third options is the best, as you can easily add or delete bosses and difficulties, but you will need more sql queries to perform the same as you would do with the first option

To store bosses and difficulties the third option is by far the best.

But sometimes the first option is better. For example

If you want to store the attributes that the players have eg, strength, agility, intelligence, hit_points, mana_points, stamina etc I would go with the first option, and put them on a seperate table, not with all the other properties of a player (email, password, verification_code etc).

I would add a separate table eg: player_attributes (player_id, strength, agility, intelligence)

and not a table with the third strategy player_attributes (player_id, attribute_id, value)

Using this option would allow you to perform queries faster and on smaller indexes