Please see the analysis below and let me know the best db design (InnoDB) out of the two. Requirement- Faster Write and Read for users not having to wait when many concurrent DB connections exists, which are expected to increase exponentially. Disk space advantage is irrelevant if users have to wait. Assumption – single CPU (just for 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 is not in order) 1.Write to table M1 - Direct Write UserID, City, State, Country = t1 M2-(Search Table2b to see record exists =t2+ Insert if no match in Table2b= t1 + Write UserID and LocationsID in Table 2a = t3) (t1+t2+t3) > t1 2. CPU Interrupts M1 =1 , M2=2 3 Disk I/O M1=1, M2=2 4.Disk space M1-More , M2-less (Only advantage in M2) Read (Assuming record not in Cache) 1. Read from Table M1-Direct read =t4 , M2-Join= t5 t5>t4 2. CPU Interrupts M1=1,M2=2 3. Disk I/O M1=1,M2=2 I believe, time spent in Method2 can be improved if Table2b is pre-populated or if Country, State, City dropdowns are numerically tagged. Even if you load balance M1 seems to be an attractive design. Increasing BW may worsen the situation as there will be more concurrent DB connections. Let me know your thoughts
M1 will be faster. The main advantage of M2 is not disk space, but the integrity of data and faster reports based on location if any.