MySQL – How to Normalize a Table with Over 250 Boolean Columns

I have a database that has 253 tinyint columns (1), a column for each country in the world.

The table has more than 3k records and rising. Is there a way to better manage this data save?

I thought of two solutions:

Create a pivot table between the brand and country table, creating a record only for true values by deleting false ones. Each brand has 15 country enabled so for example with 2k brand I would have a pivot table with 20k records.
Simply create another table with only 253 columns so you can join to get true / false data.

Do you have any advice to give me? I suggest that access to the db is run via eloquent with query graphql.

This is my table structure:

 CREATE TABLE IF NOT EXISTS `brand` (
  `id` int(11) unsigned NOT NULL,
  `userID` int(11) unsigned DEFAULT NULL,
  `name` varchar(75) COLLATE utf8_bin NOT NULL,
  `image` varchar(100) COLLATE utf8_bin NOT NULL,
  `_it` tinyint(1) NOT NULL DEFAULT '0',
  `_gb` tinyint(1) NOT NULL DEFAULT '0',
  `_ru` tinyint(1) NOT NULL DEFAULT '0',
  `_us` tinyint(1) NOT NULL DEFAULT '0',
  `_sg` tinyint(1) NOT NULL DEFAULT '0',
  `_in` tinyint(1) NOT NULL DEFAULT '0',
  `_fr` tinyint(1) NOT NULL DEFAULT '0',
  `_ca` tinyint(1) NOT NULL DEFAULT '0',
  `_ch` tinyint(1) NOT NULL DEFAULT '0',
  `_se` tinyint(1) NOT NULL DEFAULT '0',
  `_br` tinyint(1) NOT NULL DEFAULT '0',
  `_pt` tinyint(1) NOT NULL DEFAULT '0',
  `_fi` tinyint(1) NOT NULL DEFAULT '0',
  `_cn` tinyint(1) NOT NULL DEFAULT '0',
  `_ie` tinyint(1) NOT NULL DEFAULT '0',
  `_au` tinyint(1) NOT NULL DEFAULT '0',
  `_zw` tinyint(1) NOT NULL DEFAULT '0',
  `_zm` tinyint(1) NOT NULL DEFAULT '0',
  `_za` tinyint(1) NOT NULL DEFAULT '0',
  `_yt` tinyint(1) NOT NULL DEFAULT '0',
  `_ye` tinyint(1) NOT NULL DEFAULT '0',
  `_ws` tinyint(1) NOT NULL DEFAULT '0',
  `_wf` tinyint(1) NOT NULL DEFAULT '0',
  `_vu` tinyint(1) NOT NULL DEFAULT '0',
  `_vn` tinyint(1) NOT NULL DEFAULT '0',
  `_vi` tinyint(1) NOT NULL DEFAULT '0',
  `_vg` tinyint(1) NOT NULL DEFAULT '0',
  `_ve` tinyint(1) NOT NULL DEFAULT '0',
  `_vc` tinyint(1) NOT NULL DEFAULT '0',
  `_va` tinyint(1) NOT NULL DEFAULT '0',
  `_uz` tinyint(1) NOT NULL DEFAULT '0',
  `_uy` tinyint(1) NOT NULL DEFAULT '0',
  `_um` tinyint(1) NOT NULL DEFAULT '0',
  `_ug` tinyint(1) NOT NULL DEFAULT '0',
  `_ua` tinyint(1) NOT NULL DEFAULT '0',
  `_tz` tinyint(1) NOT NULL DEFAULT '0',
  `_tw` tinyint(1) NOT NULL DEFAULT '0',
  `_tv` tinyint(1) NOT NULL DEFAULT '0',
  `_tt` tinyint(1) NOT NULL DEFAULT '0',
  `_tr` tinyint(1) NOT NULL DEFAULT '0',
  `_to` tinyint(1) NOT NULL DEFAULT '0',
  `_tn` tinyint(1) NOT NULL DEFAULT '0',
  `_tm` tinyint(1) NOT NULL DEFAULT '0',
  `_tl` tinyint(1) NOT NULL DEFAULT '0',
  `_tk` tinyint(1) NOT NULL DEFAULT '0',
  `_tj` tinyint(1) NOT NULL DEFAULT '0',
  `_th` tinyint(1) NOT NULL DEFAULT '0',
  `_tg` tinyint(1) NOT NULL DEFAULT '0',
  `_tf` tinyint(1) NOT NULL DEFAULT '0',
  `_td` tinyint(1) NOT NULL DEFAULT '0',
  `_tc` tinyint(1) NOT NULL DEFAULT '0',
  `_sz` tinyint(1) NOT NULL DEFAULT '0',
  `_sy` tinyint(1) NOT NULL DEFAULT '0',
  `_sx` tinyint(1) NOT NULL DEFAULT '0',
  `_sv` tinyint(1) NOT NULL DEFAULT '0',
  `_st` tinyint(1) NOT NULL DEFAULT '0',
  `_ss` tinyint(1) NOT NULL DEFAULT '0',
  `_sr` tinyint(1) NOT NULL DEFAULT '0',
  `_so` tinyint(1) NOT NULL DEFAULT '0',
  `_sn` tinyint(1) NOT NULL DEFAULT '0',
  `_sm` tinyint(1) NOT NULL DEFAULT '0',
  `_sl` tinyint(1) NOT NULL DEFAULT '0',
  `_sk` tinyint(1) NOT NULL DEFAULT '0',
  `_sj` tinyint(1) NOT NULL DEFAULT '0',
  `_si` tinyint(1) NOT NULL DEFAULT '0',
  `_sh` tinyint(1) NOT NULL DEFAULT '0',
  `_sd` tinyint(1) NOT NULL DEFAULT '0',
  `_sc` tinyint(1) NOT NULL DEFAULT '0',
  `_sb` tinyint(1) NOT NULL DEFAULT '0',
  `_sa` tinyint(1) NOT NULL DEFAULT '0',
  `_rw` tinyint(1) NOT NULL DEFAULT '0',
  `_rs` tinyint(1) NOT NULL DEFAULT '0',
  `_ro` tinyint(1) NOT NULL DEFAULT '0',
  `_re` tinyint(1) NOT NULL DEFAULT '0',
  `_qa` tinyint(1) NOT NULL DEFAULT '0',
  `_py` tinyint(1) NOT NULL DEFAULT '0',
  `_pw` tinyint(1) NOT NULL DEFAULT '0',
  `_ps` tinyint(1) NOT NULL DEFAULT '0',
  `_pr` tinyint(1) NOT NULL DEFAULT '0',
  `_pn` tinyint(1) NOT NULL DEFAULT '0',
  `_pm` tinyint(1) NOT NULL DEFAULT '0',
  `_pl` tinyint(1) NOT NULL DEFAULT '0',
  `_pk` tinyint(1) NOT NULL DEFAULT '0',
  `_ph` tinyint(1) NOT NULL DEFAULT '0',
  `_pg` tinyint(1) NOT NULL DEFAULT '0',
  `_pf` tinyint(1) NOT NULL DEFAULT '0',
  `_pe` tinyint(1) NOT NULL DEFAULT '0',
  `_pa` tinyint(1) NOT NULL DEFAULT '0',
  `_om` tinyint(1) NOT NULL DEFAULT '0',
  `_nz` tinyint(1) NOT NULL DEFAULT '0',
  `_nu` tinyint(1) NOT NULL DEFAULT '0',
  `_nr` tinyint(1) NOT NULL DEFAULT '0',
  `_np` tinyint(1) NOT NULL DEFAULT '0',
  `_no` tinyint(1) NOT NULL DEFAULT '0',
  `_nl` tinyint(1) NOT NULL DEFAULT '0',
  `_ni` tinyint(1) NOT NULL DEFAULT '0',
  `_ng` tinyint(1) NOT NULL DEFAULT '0',
  `_nf` tinyint(1) NOT NULL DEFAULT '0',
  `_ne` tinyint(1) NOT NULL DEFAULT '0',
  `_nc` tinyint(1) NOT NULL DEFAULT '0',
  `_na` tinyint(1) NOT NULL DEFAULT '0',
  `_mz` tinyint(1) NOT NULL DEFAULT '0',
  `_my` tinyint(1) NOT NULL DEFAULT '0',
  `_mx` tinyint(1) NOT NULL DEFAULT '0',
  `_mw` tinyint(1) NOT NULL DEFAULT '0',
  `_mv` tinyint(1) NOT NULL DEFAULT '0',
  `_mu` tinyint(1) NOT NULL DEFAULT '0',
  `_mt` tinyint(1) NOT NULL DEFAULT '0',
  `_ms` tinyint(1) NOT NULL DEFAULT '0',
  `_mr` tinyint(1) NOT NULL DEFAULT '0',
  `_mq` tinyint(1) NOT NULL DEFAULT '0',
  `_mp` tinyint(1) NOT NULL DEFAULT '0',
  `_mo` tinyint(1) NOT NULL DEFAULT '0',
  `_mn` tinyint(1) NOT NULL DEFAULT '0',
  `_mm` tinyint(1) NOT NULL DEFAULT '0',
  `_ml` tinyint(1) NOT NULL DEFAULT '0',
  `_mk` tinyint(1) NOT NULL DEFAULT '0',
  `_mh` tinyint(1) NOT NULL DEFAULT '0',
  `_mg` tinyint(1) NOT NULL DEFAULT '0',
  `_mf` tinyint(1) NOT NULL DEFAULT '0',
  `_me` tinyint(1) NOT NULL DEFAULT '0',
  `_md` tinyint(1) NOT NULL DEFAULT '0',
  `_mc` tinyint(1) NOT NULL DEFAULT '0',
  `_ma` tinyint(1) NOT NULL DEFAULT '0',
  `_ly` tinyint(1) NOT NULL DEFAULT '0',
  `_lv` tinyint(1) NOT NULL DEFAULT '0',
  `_lu` tinyint(1) NOT NULL DEFAULT '0',
  `_lt` tinyint(1) NOT NULL DEFAULT '0',
  `_ls` tinyint(1) NOT NULL DEFAULT '0',
  `_lr` tinyint(1) NOT NULL DEFAULT '0',
  `_lk` tinyint(1) NOT NULL DEFAULT '0',
  `_li` tinyint(1) NOT NULL DEFAULT '0',
  `_lc` tinyint(1) NOT NULL DEFAULT '0',
  `_lb` tinyint(1) NOT NULL DEFAULT '0',
  `_la` tinyint(1) NOT NULL DEFAULT '0',
  `_kz` tinyint(1) NOT NULL DEFAULT '0',
  `_ky` tinyint(1) NOT NULL DEFAULT '0',
  `_kw` tinyint(1) NOT NULL DEFAULT '0',
  `_kr` tinyint(1) NOT NULL DEFAULT '0',
  `_kp` tinyint(1) NOT NULL DEFAULT '0',
  `_kn` tinyint(1) NOT NULL DEFAULT '0',
  `_km` tinyint(1) NOT NULL DEFAULT '0',
  `_ki` tinyint(1) NOT NULL DEFAULT '0',
  `_kh` tinyint(1) NOT NULL DEFAULT '0',
  `_kg` tinyint(1) NOT NULL DEFAULT '0',
  `_ke` tinyint(1) NOT NULL DEFAULT '0',
  `_jp` tinyint(1) NOT NULL DEFAULT '0',
  `_jo` tinyint(1) NOT NULL DEFAULT '0',
  `_jm` tinyint(1) NOT NULL DEFAULT '0',
  `_je` tinyint(1) NOT NULL DEFAULT '0',
  `_is` tinyint(1) NOT NULL DEFAULT '0',
  `_ir` tinyint(1) NOT NULL DEFAULT '0',
  `_iq` tinyint(1) NOT NULL DEFAULT '0',
  `_io` tinyint(1) NOT NULL DEFAULT '0',
  `_im` tinyint(1) NOT NULL DEFAULT '0',
  `_il` tinyint(1) NOT NULL DEFAULT '0',
  `_id` tinyint(1) NOT NULL DEFAULT '0',
  `_hu` tinyint(1) NOT NULL DEFAULT '0',
  `_ht` tinyint(1) NOT NULL DEFAULT '0',
  `_hr` tinyint(1) NOT NULL DEFAULT '0',
  `_hn` tinyint(1) NOT NULL DEFAULT '0',
  `_hm` tinyint(1) NOT NULL DEFAULT '0',
  `_hk` tinyint(1) NOT NULL DEFAULT '0',
  `_gy` tinyint(1) NOT NULL DEFAULT '0',
  `_gw` tinyint(1) NOT NULL DEFAULT '0',
  `_gu` tinyint(1) NOT NULL DEFAULT '0',
  `_gt` tinyint(1) NOT NULL DEFAULT '0',
  `_gs` tinyint(1) NOT NULL DEFAULT '0',
  `_gr` tinyint(1) NOT NULL DEFAULT '0',
  `_gq` tinyint(1) NOT NULL DEFAULT '0',
  `_gp` tinyint(1) NOT NULL DEFAULT '0',
  `_gn` tinyint(1) NOT NULL DEFAULT '0',
  `_gm` tinyint(1) NOT NULL DEFAULT '0',
  `_gl` tinyint(1) NOT NULL DEFAULT '0',
  `_gi` tinyint(1) NOT NULL DEFAULT '0',
  `_gh` tinyint(1) NOT NULL DEFAULT '0',
  `_gg` tinyint(1) NOT NULL DEFAULT '0',
  `_gf` tinyint(1) NOT NULL DEFAULT '0',
  `_ge` tinyint(1) NOT NULL DEFAULT '0',
  `_gd` tinyint(1) NOT NULL DEFAULT '0',
  `_ga` tinyint(1) NOT NULL DEFAULT '0',
  `_fo` tinyint(1) NOT NULL DEFAULT '0',
  `_fm` tinyint(1) NOT NULL DEFAULT '0',
  `_fk` tinyint(1) NOT NULL DEFAULT '0',
  `_fj` tinyint(1) NOT NULL DEFAULT '0',
  `_et` tinyint(1) NOT NULL DEFAULT '0',
  `_es` tinyint(1) NOT NULL DEFAULT '0',
  `_er` tinyint(1) NOT NULL DEFAULT '0',
  `_eh` tinyint(1) NOT NULL DEFAULT '0',
  `_eg` tinyint(1) NOT NULL DEFAULT '0',
  `_ee` tinyint(1) NOT NULL DEFAULT '0',
  `_ec` tinyint(1) NOT NULL DEFAULT '0',
  `_dz` tinyint(1) NOT NULL DEFAULT '0',
  `_do` tinyint(1) NOT NULL DEFAULT '0',
  `_dm` tinyint(1) NOT NULL DEFAULT '0',
  `_dk` tinyint(1) NOT NULL DEFAULT '0',
  `_dj` tinyint(1) NOT NULL DEFAULT '0',
  `_de` tinyint(1) NOT NULL DEFAULT '0',
  `_cz` tinyint(1) NOT NULL DEFAULT '0',
  `_cy` tinyint(1) NOT NULL DEFAULT '0',
  `_cx` tinyint(1) NOT NULL DEFAULT '0',
  `_cw` tinyint(1) NOT NULL DEFAULT '0',
  `_cv` tinyint(1) NOT NULL DEFAULT '0',
  `_cu` tinyint(1) NOT NULL DEFAULT '0',
  `_cr` tinyint(1) NOT NULL DEFAULT '0',
  `_co` tinyint(1) NOT NULL DEFAULT '0',
  `_cm` tinyint(1) NOT NULL DEFAULT '0',
  `_cl` tinyint(1) NOT NULL DEFAULT '0',
  `_ck` tinyint(1) NOT NULL DEFAULT '0',
  `_ci` tinyint(1) NOT NULL DEFAULT '0',
  `_cg` tinyint(1) NOT NULL DEFAULT '0',
  `_cf` tinyint(1) NOT NULL DEFAULT '0',
  `_cd` tinyint(1) NOT NULL DEFAULT '0',
  `_cc` tinyint(1) NOT NULL DEFAULT '0',
  `_bz` tinyint(1) NOT NULL DEFAULT '0',
  `_by` tinyint(1) NOT NULL DEFAULT '0',
  `_bw` tinyint(1) NOT NULL DEFAULT '0',
  `_bv` tinyint(1) NOT NULL DEFAULT '0',
  `_bt` tinyint(1) NOT NULL DEFAULT '0',
  `_bs` tinyint(1) NOT NULL DEFAULT '0',
  `_bq` tinyint(1) NOT NULL DEFAULT '0',
  `_bo` tinyint(1) NOT NULL DEFAULT '0',
  `_bn` tinyint(1) NOT NULL DEFAULT '0',
  `_bm` tinyint(1) NOT NULL DEFAULT '0',
  `_bl` tinyint(1) NOT NULL DEFAULT '0',
  `_bj` tinyint(1) NOT NULL DEFAULT '0',
  `_bi` tinyint(1) NOT NULL DEFAULT '0',
  `_bh` tinyint(1) NOT NULL DEFAULT '0',
  `_bg` tinyint(1) NOT NULL DEFAULT '0',
  `_bf` tinyint(1) NOT NULL DEFAULT '0',
  `_be` tinyint(1) NOT NULL DEFAULT '0',
  `_bd` tinyint(1) NOT NULL DEFAULT '0',
  `_bb` tinyint(1) NOT NULL DEFAULT '0',
  `_ba` tinyint(1) NOT NULL DEFAULT '0',
  `_az` tinyint(1) NOT NULL DEFAULT '0',
  `_ax` tinyint(1) NOT NULL DEFAULT '0',
  `_aw` tinyint(1) NOT NULL DEFAULT '0',
  `_at` tinyint(1) NOT NULL DEFAULT '0',
  `_as` tinyint(1) NOT NULL DEFAULT '0',
  `_ar` tinyint(1) NOT NULL DEFAULT '0',
  `_aq` tinyint(1) NOT NULL DEFAULT '0',
  `_ao` tinyint(1) NOT NULL DEFAULT '0',
  `_am` tinyint(1) NOT NULL DEFAULT '0',
  `_al` tinyint(1) NOT NULL DEFAULT '0',
  `_ai` tinyint(1) NOT NULL DEFAULT '0',
  `_ag` tinyint(1) NOT NULL DEFAULT '0',
  `_af` tinyint(1) NOT NULL DEFAULT '0',
  `_ae` tinyint(1) NOT NULL DEFAULT '0',
  `_ad` tinyint(1) NOT NULL DEFAULT '0',
  `creation_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `disabled` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB AUTO_INCREMENT=2556 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Best Answer

Your mistake is that you are trying to include two entities (country and brand) in one table - this has created the monster that you've described above.

If I were you, I'd start with a design like this!

CREATE TABLE country
(
  country_id INTEGER AUTO_INCREMENT PRIMARY KEY,
  country_name VARCHAR (50) NOT NULL

  -- other fields...
);

CREATE TABLE brand
(
  brand_id INTEGER AUTO_INCREMENT PRIMARY KEY,
  brand_name VARCHAR (50) NOT NULL,
  brand_image VARCHAR (100), -- why is an image a VARCHAR (100)? file location?
  brand_created DATE NOT NULL,
  brand_enabled TINYINT(1)
);

CREATE TABLE country_brand
(
  cb_country_id SMALLINT NOT NULL REFERENCES country (country_id),
  cb_brand_id SMALLINT NOT NULL REFERENCES brand (brand_id),
  cb_brand_enabled TINYINT(1)  NOT NULL,
  CONSTRAINT cb_pk PRIMARY KEY (cb_country_id, cb_brand_id),

  -- I always like explicitly naming my PRIMARY KEYs - error messages are so much more meaningful!
  UNIQUE INDEX cb_pk_rev_uq (cb_brand_id, cb_country_id)

  -- country_brand_primary_key_reverse_unique index
);

The key here is a joining table in a many to many relationship - i.e. between countries and brands.

A response to the query about performance.

A table with ~ 750,000 records and 3 fields is way more preferable to one with 3000 records and 250 fields!

You can index country.country_name and also brand.brand_name - if your performance is poor - but I think it will be much better than your previous setup!

750,000 is tiny by today's standards - plus the amount of data is unchanged, it's just far better organised! With an index, searches will be in the millisecond range!

Thanks for @RickJames for his tips from here.

Best Answer

Related Solutions

Mysql – Unexplained InnoDB timeouts

Mysql – Are two indexes needed

Related Question