Should I normalize my string arrays, or just keep it a simple string? 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.
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.