Is it bad to not use normalised tables in this database?

Question

I recently learned about normalisation in my informatics class and I'm developing a multiplayer game using SQLite as backend database at the moment.

Some information on it:

The simplified structure looks a bit like the following:

 player_id |  level | exp  | money | inventory
---------------------------------------------------------
    1      |    3   | 120  | 400   | {item a; item b; item c}

Okay. As you can see, I'm storing a table/array in string form in the column "inventory". This is against normalization.

But the thing is: Making an extra table for the inventory of players brings only disadvantages for me!

• The only points where I access the database is:
  • When a player joins the game and his profile is loaded
  • When a player's profile is saved

When a player joins, I load his data from the DB and store it in memory. I only write to the DB like every five minutes when the player is saved. So there are actually very few SQL queries in my script.

If I used an extra table for the inventory I would have to, upon loading:

• Perform an performance and probably more data-intensive query to fetch all items from the inventory table which belong to player X
• Walk through the results and convert them into a table for storage in memory

And upon saving:

• Delete all items from the inventory table which belong to player X (player might have dropped/sold some items?)
• Walk through the table and perform a query for each item the player owns

If I kept all the player data in one table:

• I'd only have one query for saving and loading
• Everything would be in one place
• I would only have to (de)serialize the tables upon loading and saving, in my script

What should I do now?

Do my arguments and situation justify working against normalisation?

Answer 1

Are you saying that you think parsing a string out of "inventory" doesn't take any time or effort? Because everything you need to do to store/retrieve inventory items from a sub table is something you'd need to do with this string, and with the string you don't have any database tools to help you do it.

Also, if you had a separate subtable for inventory items, you could add and remove items in real time, meaning that if the app crashes or the user disconnects, they don't lose anything.

Answer 2

There are a lot of possible answers, but the one that works for you is the one to choose. Keep in mind, your choice may need to change over time.

If the amount of data you need to persist is small (ie: fits into a single table row) and you only need to update that data infrequently, and you don't have any reason to care about subsets of that data, then your approach makes sense. As time goes on and your players gain more items and you add more personalization to the game, you may begin to push up against the limits of SQLite, and you'll need to evolve your design. If you discover that you need to be able to query the item list to determine which players have what items, you'll need to evolve your design.

It's generally considered a good idea to get your data architecture right early, but there's no point in sitting in meetings today trying to guess how you'll use your software in 5-10 years. Better to get a design that meets this year's needs, and then plan to re-evaluate the design again after a year.