I'm fairly new to the databases and I'm now creating little project where I have a database and I want to store deck of cards into the table where each card has its own ID (for example 52 cards in a deck).

What would be the best way to go about it, performance wise?

I have two ideas:

  1. The table "deck" will have column with varchar where I will store cards in CSV format with help of backend language
  2. New table will be created with 52 columns for every card and store them there

Thanks for any help.

  • 2
    Do not store multiple values in a single column. You might think this is clever but, trust me, you will pay for it in the long run when you start searching, joining, updating, etc. You're using a relational database (well, with MySQL perhaps that's a bit generous) presumably because you don't want to use a dumb JSON store or text files. You also don't want 52 columns. Why aren't these 52 ROWS? – Aaron Bertrand Apr 10 '15 at 23:39
  • I should have said, that those values will be for displaying only. Maybe in future I'd like to make them editable. But as you said, I don't want JSON in MySQL unless necessary. And 52 rows is also not good idea, because I want to implement few types of cards that user can have. – Aadam Apr 10 '15 at 23:48
  • 2
    That seems to be exactly why you would want rows and not columns. Are you going to add 52 columns to your table every time you have a new type of game? – Aaron Bertrand Apr 11 '15 at 0:17
  • Yes, you are right. I was concerned by having too many rows, but I read up on it and that will not be problem even with few thousand decks. Thanks for help. – Aadam Apr 11 '15 at 8:47

There is an art and a science to database design. The science is normalisation. If you're intent on learning about databases you will need a solid understanding of it.

The art is in deciding just what exactly the "things" are that are going into your database. Defining them in a why that is comprehensive and precise is a skill to learn.

In your particular case, storing multiple card ids in a single column prima face breaks first normal form. This is where the "art" bit comes in. If you only ever consider these cards all together - say, as a "hand" or a "pack" - and never as individual items then it would be legitimate to model them as a single column. If you ever want to consider each card individually ("How many times has the Queen of hearts been delt?") then you would be better of modelling a seperate table with one row per card.

A second consideration is performance. It will always be faster to insert or delete one row than 52. It will almost always be slower to pull a substring out of a long varchar than to pick a single row from a large, well-indexed table. These considerations conflict. On balance, I would always recommend the one-row-per-card approach until it was proven otherwise.

You are best placed to judge the usage and volumes of your application and, hence, resolve the conflicts.

| improve this answer | |

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.