database - MySql DB Design -Which is the best of the two- Normalized or Not -

please see analysis below , let me know best db design (innodb) out of two. requirement- faster write , read users not having wait when many concurrent db connections exists, expected increase exponentially. disk space advantage irrelevant if users have wait.

assumption – single cpu (just comparison)

method 1 (m1) table1 userprofile -> userid, city, state, country

method2 (m2)(normalized) table2a userprofile->userid,locationsid table2b locations-> locationsid, city, state, country

write (sequence not in order)

a. write table

m1-direct write= t1 m2-(search table2b see record exists=t2+ insert if no match=t1 write userid , locationsid in table 2a=t3) (t1+t2+t3) > t1

b.cpu interrupts

m1=1,m2=2

c.disk i/o

m1=1,m2=2

d.row locks & releases

m1=1,m2=2

e. disk space

m1=more, m2=less(only advantege in m2)

read (assuming record not in cache)

a. read table

m1-direct read=t4, m2-join-t5 t5>t4

b. cpu interrupts

m1=1, m2=2

c.disk i/o

m1=1,m2=2

i believe, time spent in method2 can improved if table2b pre-populated or if country, state, city dropdowns numerically tagged. if load balance m1 seems attractive design. increasing bw may worsen situation there more concurrent db connections. let me know thoughts

method2 (m2)(normalized) table2a userprofile->userid,locationsid table2b locations-> locationsid, city, state, country

you have replaced city, state, , country id number. while might design decision in cases, it's not design decision. , has nothing normalization. (there's no such thing "i used id number" normal form.)

when there's international standard, makes sense use it. see iso 3166-1. three-letter codes might make more sense.

-- untested code. create table countries (   iso_country_code char(2) not null,   country_name varchar(35) not null,   primary key (iso_country_code),   unique (country_name) );  create table states (   state_code char(2) not null,          -- application-dependent, consider iso 3166-2   state_abbrev varchar(7) not null,   state_name varchar(35) not null,   iso_country_code char(2) not null,   primary key (state_code, iso_country_code),   unique (state_abbrev, iso_country_code),   unique (state_name, iso_country_code),   foreign key (iso_country_code) references countries (iso_country_code) );  create table cities (   city_name varchar(35) not null,   state_code char(2) not null,   iso_country_code char(2) not null,   primary key (city_name, state_code, iso_country_code),   foreign key (state_code, iso_country_code)      references states (state_code, iso_country_code) );  create table userprofile (   userid integer not null,   city_name varchar(35) not null,   state_code char(2) not null,   iso_country_code char(2) not null,   primary key (userid),   foreign key (city_name, state_code, iso_country_code)      references cities (city_name, state_code, iso_country_code) ); 

separate tables countries, states, , cities make easy populate combo boxes select statements. need no numeric "tags". 3 of tables key; have no non-prime attributes. think they're in 5nf.

as rule of thumb, don't search row see whether exists, insert if doesn't. requires 2 round-trips database.

instead, insert row, , trap error you'll if it's duplicate. have trap errors anyway--there lot of things can keep insert succeeding besides duplication.