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 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. Commented Sep 18, 2017 at 20:40
  • @emindemiri - just wondering how this new table structure worked out for you? 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 Commented Oct 27, 2017 at 8:29

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.