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

What's going to happen when you have one hundred thousand items in your inventory and you only want to bring back two?

If this is something that you're throwing together for a one off class and that you won't ever use again, then yes, the quick and dirty route might be a quicker option for you.

However if this is something you're going to be working on for a few months, then you're going to run into long-term issues with that design decision.

Answer 3

No, your arguments aren't valid. They basically boil down to "I want to do all of this processing in my client code instead of in SQL and then just write it all to a single field" because you are still doing all of the exact same processing to generate the string. By doing this you are removing the ability to easily load a small portion of the list and losing relationships to the actual item table which could contain more information about the items (I assume you're hard coding it all based on names instead of using internal item IDs which is a really bad idea, imo).

Don't do it. Long term the approach you are wanting to take will generate a lot more work for you as your needs evolve.

Answer 4

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.

Answer 5

You should also think about the items. Are the items unique for every user or does user1 could have item1 and user2 have item1 to. If you now want to change item1 you have to go through your whole table and check which user have this item. If you would normalize your table, this would be much more easy.

But it the end, I think the answer is: It depends

Answer 6

Another case of premature optimization.

You are trying to optimize something that you don't have any performance metrics. What is the target platform? Even crappiest computers nowadays could run at least hundreds of your reading operation per second. Then you add better hardware for more users, then you can go to cloud and when you come into problem space that Google, Twitter and Facebook are dealing with, you can consider denormalizing. Even then, best solution is some sort of key-value database.

Maybe you should check Wikipedia article on Database Normalization to remind you why normalized database is a good thing.

Answer 7

Do my arguments and situation justify working against normalisation?

Not based on what I've seen so far.

Normalized database designs (appropriately indexed and with efficient usage of the database with UPSERTS, transactions, etc) in general-purpose engines will generally outperform code except where code is very tightly optimized. Typically in such code, some feature of the general purpose RDBMS engine is abandoned, such as one of the ACID properties or referntial integrity.

If you want to have very simple data access (you tout one table, one query as a benefit), perhaps you should look at a document centric database like mongodb or couchdb.

Answer 8

The reason that you use any technology is to leverage the technology's advantages. SQL has many advantages that you seem to not want to use, and that's fine, if you don't need them. In Neal Stephenson's Zodiac, the main character mentions that few things bought from a hardware store are used for their intended purpose. Software's like that, too. What counts is that it works, and it works nearly 100% of the time, and it works fast enough.

And yet, I can't help but think that someday you're going to have some overpowered item released into the wild, and you're going to want to deal with this problem at the database layer. Say you accidently gave out some superinstakillmegadeathsword inventory items that kill everything within 50 meters on use (wielder included), and you want to remove those things from play. As an apology to the people who lose their superinstakillmegadeathsword items, you want to give them 100 money for each superinstakillmegadeathsword you take away.

With a properly normalized database structure, that's a trivial task. With a denormalized structure, it's quite a bit harder and slower. A normalized database is also going to be easier to expand on the design in the future.

So are you sure you don't want to normalize your database?