SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Thread: DB design

  1. #1
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    DB design

    DB structure1

    I have a table like the following.
    The table has 100,000 records.
    The table has 10,000 types.
    A type has 10 records each.

    (ID) type
    (1) 7,519
    (2) 1
    (3) 10,000
    ) :
    ) :
    (99999) 3,275
    (100000) 2

    DB structure2

    I have 10,000 tables.
    Each table has 10 records.

    table1
    (ID)
    (1)
    )
    (10)

    table2
    (ID)
    (1)
    )
    (10)

    :
    :
    table9999
    (ID)
    (1)
    )
    (10)

    table10000
    (ID)
    (1)
    )
    (10)


    Let's suppose;
    the site has 1,000 requests concurrently.
    each request retrieve 10 records of the same type in DB structure1 or 10 records from a table in DB structure2.


    The outputs of the DB structure1 is the same of results of the DB structure2.

    My question is which DB structure is more efficient or has advantage.


    My guess1:
    DB structure1 is more convinient because 10,000 tables are not easy to manage.

    my guess2:
    DB structure2 is more efficient because all 1000 requests are concentrated on in A table in DB structure1 while the 1000 requests are diversed on every table in DB structure2.


    Is my guess2 right?

    Which would you prefer?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    i cannot comment on the performance without actually testing both scenarios, and i'm not going to do that

    my guess is that they will perform the same

    however, the 2nd design is terrible, and i would never use it

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •