Array or Database Table?

Question

I could really use some good feedback on best practices / insight on whether it is better to put things in the database, or build an array. Take for example you have the suits of cards:

$suits = array('clubs', 'spades', 'hearts', 'diamonds');

The array could change in the future, but not very frequently if at all. Now these suits are applied to the cards in a deck. So if I build a table, the schema would look something like:

create table cards {
   id INT(11)
   card_value char(2),
   suit varchar(8)
}

Or is it better to build a suits table and reference the ID in the cards table like:

create table suits {
   id INT(11),
   name varchar(8)
}

create table cards {
   id INT(11),
   suit_id int(11),
   card_value char(2)
}

Which is the better way to go and why?

Answer 1

Typically I'd say you're best off storing the information in the database, although in the cards example I'd be tempted to either have the information in code and store a 'card number' calculated from (suit_idx * 13 + card_value), or use the cards table you gave in your second code fragment - as cards are highly unlikely to change (especially once you've started using them in code).

At the end of the day - if you're going to be performing queries against e.g. 'hands' of cards, or against a deck of cards in the database then you're going to want to use either of your SQL representations - with the former being just that little bit easier to query (although with a view that joins card to suit, either option is easy to query - although I'd still be tempted to stick with the first SQL version).

Answer 2

Having read your comments to Will's response I know Suits and Cards wasn't your first choice of example, but I am going to pursue the example anyway :)

I agree it is unlikely the values of suits are going to change, unless you want a system that is flexible enough to handle Tarot cards (Swords, Staves, Cups and Coins). Nevertheless there are benefits to holding the data in a table.

The first is that the Suit is used in several places. Card games like Bridge and Whist need to know which Suit is trumps. Hearts (Black Maria, Chase the Lady) is a game which needs to be able to distinguish the cards of certain suits which carry penalties. So, there are several places where referencing SUITS.ID would be useful.

The second benefit is that Suits have more attributes than just name. Some games rank suits, for instance in Bridge a bid of Three Spades wins over a bid of Three Hearts. If we're building a card game application we might what to associate an image file or wingding character to represent the card symbol.

Both these considerations point towards your second design. It is the only one which avoids duplication and allows us to enforce integrity constraints.