1

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:

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

  2. 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;
Improve this question
0

1 Answer 1

Reset to default
2

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.

Improve this answer
4
  • very good thanks, tomorrow as soon as I can try with data I will let you know the times of reading and writing
    – emin demiri
    Commented Sep 18, 2017 at 20:37
  • Not only will performance improve but if a new country is formed it will be much easier to add it to the database with inserts instead of modifying a table.
    – Joe W
    Commented Sep 18, 2017 at 20:40
  • @emindemiri - just wondering how this new table structure worked out for you?
    – Vérace
    Commented Oct 24, 2017 at 11:49
  • I had to freeze that project, as soon as I can work, I'll let you know. Thank you
    – emin demiri
    Commented Oct 27, 2017 at 8:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.