1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

DB Design-Pick best out of two-Normalized or Not

Discussion in 'Databases' started by sunilwije, May 13, 2013.

  1. #1
    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
     
    Last edited: May 13, 2013
    sunilwije, May 13, 2013 IP
  2. PYO

    PYO Member

    Messages:
    38
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    36
    #2
    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.
     
    PYO, Jun 5, 2013 IP