MySQL Performance – Storing Multiple IDs in Column or New Table

csvMySQLperformance

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:

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

Thanks for any help.

Best Answer

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.

Related Solutions

Apply in a CSV file to multiple databases

Load the CSV file into a "staging" table. From there you can easily do the UPDATE/INSERT/DELETE in a single statement for each one

Something along the lines:

update real_table
  set ...
where exists (select 1 from staging_table where ...)

insert into ...
select 
from stage_table
  left join real_table 
where real_table.some_col is NULL

delete real_table
where not exists (select 1 from staging_table)

Then delete the data from the staging table.

Probably the fastest way to do it. The only drawback is that you have the data for one table twice in the database (until it is deleted from the staging table).

MySQL Localization – One Table or Multiple Tables?

This is clear example of Many-to-Many relation (M:N).
Many messages can have (be translated into) many languages.

Date structure for that has three tables:

create table Message (
  ID int primary key, 
  message text
);

create table Language (
  ID int primary key,
  language varchar(50)
);

create table Message_Language (
  message_id int, 
  language_id int,
  contraint Message_Language_Message_fk foreign key(message_id) references Message(id),
  contraint Message_Language_Language_fk foreign key(language_id) references Language(id),
  constraint Message_Language_PK (message_id, language_id)
);

It's more modular and you can add more languages easily.

What kind of queries are you performing on the tables.
Provide more info about complete data structure (and other tables).

Are you aware of data normalization in RDBMS?

Best Answer

Related Solutions

Apply in a CSV file to multiple databases

MySQL Localization – One Table or Multiple Tables?

Related Question