views:

51

answers:

2

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?

+3  A: 

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).

Will A
Yeah, I couldn't think of a better example. It's more about the suits than the numbers. But isn't that how it usually works out. I should have stuck with the original idea of colors to cars. Doh!
cdburgess
Colours to cars you'd definitely store in the DB - you'll potentially be getting new cars come along all the time - and they'll always be able to dream up new colours - so your second SQL representation - normalized - is the one to go for here. :)
Will A
This isn't a normalisation issue. In the first table there isn't anything functionally dependant upon the `suit` column. If the first table had another column called `colour` then it wouldn't be normalised.
Martin Smith
...but at the same time, Martin, isn't the duplication of the suit string to be frowned upon (in this instance probably not, but in the real world?).
Will A
@Will - I agree if it was a large table introducing a surrogate integer key in place of the string would be better for space and performance reasons I was just picking up on the use of the term `denormalized`
Martin Smith
@Martin - very fair point - not the right term to have used here - answer modified accordingly.
Will A
+2  A: 

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.

APC
This helps a lot! It helps me to see that while an array WILL work, it's not just about the immediate needs. For better extensibility, it's best to put the data in the database (regardless of how small) since it will most likely be used elsewhere in the application. And it's better to have it accessible from the database, than hard coded somewhere. Thanks for opening my mind!
cdburgess