Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Join Date
    Jun 2004
    Posts
    9

    Question Faster: 100 tables/1k names -OR- 1 table/100k names?

    Hello all.

    I am somewhat of a novice when it comes to db designs, so i think it would be prudent of me to ask this before designing my database application.

    I am designing a database for a company with 100 departments. Each department will have 1,000 Employees. Taking growth into consideration, Which database structure for the Departments would perform faster, and why?
    100 tables/1,000 names each?
    -OR-
    1 table/100,000 names (using a DEPARTMENT field)?

    Thank you in advance.

    [I will be using MySQL or MS SQL].
    Last edited by kaanuki; 06-22-06 at 19:22.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if there is any chance, even the slightest chance, that someone will ask you for a report that has to encompass data from all departments, ask yourself what the sql for that query would look like

    this should settle your question in a trice
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    No offence intended to kaanuki, but why do large organisations persist in delegating database design to self-confessed novices? Is their data really so unimportant to them?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Quote Originally Posted by andrewst
    No offence intended to kaanuki, but why do large organisations persist in delegating database design to self-confessed novices? Is their data really so unimportant to them?
    My first guess is that the organization is a college.

    -PatP

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    or... the novice figures out how to use databases, and starts developing apps. The company learns the value of gathering data, and says "Hey, could you build an app that does this?"
    Inspiration Through Fermentation

  6. #6
    Join Date
    Aug 2004
    Posts
    330
    This could be considered a security method. If someone wants to steal the data, it will be more difficult to query 100 tables.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    @urquel: chortle
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2004
    Posts
    9
    Come on guys...

    The question is MOSTLY theoretical to provide a clearer picture for you to answer my question about using efficient database designs.

    Why do you have to attempt to degrade me by analyzing me/my needs simply to show what experts you are. I already know you guys are smart that is why I came here. But I asked for help in analyzing DATABASE DESIGN, not My database knowledge/usage.

    Can someone please provide me with an intelligent answer versus an analysis?

    ...Again, Which design is more efficient in terms of speed and accessibilty?

  9. #9
    Join Date
    Jun 2004
    Posts
    9

    Arrow

    ??????????

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in terms of speed and accessibility, if the tables are properly indexed, they should be the same

    in terms of administration, or being able to retrieve summary information from the entire set of data, one of those approaches is insane

    hope that was intelligent enough for you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by kaanuki
    ps... r937 SQL Consultant,

    SELECT * FROM tblCOMPANY
    [WHERE Dept. = '...']

    Was that a trick question or something?
    no, i was serious, your query will look something like this --
    Code:
    select sum(foo) as total_foo
      from ( 
           select foo from department001
           union all
           select foo from department002
           union all
           select foo from department003
           union all
           select foo from department004
           union all
           select foo from department005
           ...
           union all
           select foo from department099
           union all
           select foo from department100
           )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Aug 2004
    Posts
    330
    This is a mistake that is frequently made in design. It is frequently incorrectly assumed that a particular design will not preform well. So, the database is designed around "assumed" performance. It is better to create a design around the nature of your data and business rules. The final physical implementation and queries can generally be tuned for performance. Splitting a table into 100 separate tables can usually be done behind the scenes by the DBMS if needed for performance.

  13. #13
    Join Date
    Jul 2006
    Posts
    3
    Normalizing your design will probably not sum up to 100 tables, but I believe its not a single table only. Even a simple design has 4 or more tables but not more likely 100 tables. Learning what DBMS is all about will be a good help.

  14. #14
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255
    Quote Originally Posted by kaanuki
    100 tables/1,000 names each?
    -OR-
    1 table/100,000 names (using a DEPARTMENT field)?
    What's wrong with 100,000 tables with one row each ?
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by DerekA
    What's wrong with 100,000 tables with one row each ?
    see post #11
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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