4

I am working on a project involving cards from the Magic: The Gathering game. To be of any use, I must load the basic information about each card into my database, but some of the fields may contain arrays of text, that is, they may contain multiple values .

For example, a card can be classified to be both "green" and "blue". Or it can have several different creature types specified, and so on.

My first thought was to simply store the values as a string, and then search using the LIKE keyword. In combination with the NOT keyword, it should be possible to search for any desired combination of words, and also exclude certain words.

However, LIKE is going to be (relatively) slow as it can't use an index and has to rely on text pattern matching, and probably has other drawbacks as well. With normalization I could improve speed by assigning an integer key to each keyword, and then create a table that connects each card with the relevant keys (see EBrowns answer for a clearer description).

But with normalized tables I have to deals with joins (severely increased code complexity, not only in the SQL part), transactions (to update all relevant tables or rollback everything), and possibly decreased performance due to joins (see To normalize or not).

AFAIK there are roughly 20000-25000 different magic cards in existence (and more being released every year). With one row for each card, is it worth it overall to normalize the data, or is the benefit not worth the costs? Why/Why not? And how do you decide this question in general?

EDIT: I am currently using a MySQL database, but I would also appreciate answers that apply to any of the other non-commercial/open source databases out there. Who knows - I might switch to a different database at some point.

Answers should not provide a technical solution only, but explain why it would/might be the right solution in terms of best/good enough performance for least amount of effort.

  • I suggest that none of the reasons from To normalize or not apply in your case. 1# you're looking at 2 JOIN's vs 0/1 JOIN and only 25k rows. Database engines are designed to handle high loads (Facebook runs on MySQL; and benchmark 40-100k inserts per second. 2# This just means that putting conditions in WHERE is same as in JOIN clauses (performance-wise). 3# You don't have large columns, MyISAM & InnoDB are both fine - just dont' mix them. – Serge Jan 26 '16 at 0:22
  • @Serge: If you would pharse your comment as an answer with a recommended approach, and explaining your reasoning like above, I would be happy to upvote it, possibly even accept it. – cornergraf Jan 27 '16 at 8:28
2

A couple of things you might want to consider:

  1. Maintenance of the data.

    If the data is going to change often then it would be easier and quicker to have the data normalised so you only have to change it in one place and have all the usages of it automatically update. Conversely, if the data hardly ever changes then this is not a consideration.

  2. Full text searching.

    This should be quicker than doing like '%string%' searches and, depending on your database and what's available, may dictate the format you have store your data in.

    In addition there's the obvious thing that storing the text repeatedly in the table(s) will increase the size of your database. This may have an impact on performance or cost you more to make sure you have enough disk space and/or memory available on your server.

    Wikipedia has an article on the basics. It's not an area I'm terribly familiar with, just that a colleague is looking into this and there are trade-offs with how the data is organised when doing other searches. Other links - MSDN, MySQL and PostgreSQL

2

Well you could attack this in the manner you previously stated, or you could go another route.

Typically, in this situation, I would create three tables.

Table 1 - Cards: This would contain the basic card information.

Table 2 - Types: This would be one record for each possible type (i.e. one record for green, one for blue, one for creature A, one for creature B, and so on.)

Table 3 - CardTypes: This would have two columns, one Foreign Key to Cards and one Foreign Key to Types. The combination of the two would be the Primary Key.

The advantage to this approach is that you don't have to store a large amount of plain-text, as if a card has multiple properties they would each go one-and-one in Types, and then a relationship to each property would go in CardTypes. It also means if two cards are blue, then only one Type is store for blue, and two (small, mind you) entries are in CardTypes.

The downfall is the obvious overhead. Your programme must be aware of the three tables.

The advantages depend entirely on how often you are querying the tables. If you are often querying for a specific property, then the index on CardTypes would significantly improve performance, as the index would contain the necessary pointers to your cards that have that property.

In the end it all depends on how often/many queries you will be running. If you are running a significant amount you will likely be better off taking the programming overhead of creating two tables and then linking them with a third.

2

Community Wiki answer generated from a comment on the question by @a-horse-with-no-name

You might want to look into Postgres' hstore data type. A very efficient (indexable!) key/value store. Plus it has index types that efficiently support like '%ab%'

PostgreSQL hstore documentation

1

For the time being I have settled on not normalizing the string arrays, and just keeping them as string columns as it has not been a problem so far. While I realize that I do not have any userbase yet to accurately judge performance, I have come to realize that it might be premature to try and performance optimize this problem until I run into actual problems.

I would still like to see further input, so if you think that there are some points I have not yet considered, please share!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.