Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2007
    Posts
    2

    Unanswered: Quick newbie design question

    Hi,

    Quick question for potential optimization of my database design.

    Is it better to have 1 big table representing a certain type of information or to break it up into a series of related tables?

    For example:

    I want a table of links from a certain number of websites. Should I have 1 big table like: links or a series of tables, 1 table representing each site?

    Opinions and potential pitfalls would be helpful on either side. Thank you.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    all the data is the same structure, use one table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    In my mind there are very few reasons for splitting the same/similar data into several tables, such as

    the number of columns is so big that it blows the limit imposed byt he SQL engine
    no one, or vitually no one) consumer of that data is going want or need to see every column, and there are security reasons to push the other stuff into another table. In my view thats a fallacious arguemnt as views can do the same effective trick, or you are using physical design to get round a servers security limitations)
    sometimes its becasue the other block/blocks of data are optional.. to me thats just bad physical design, you could always have a sub table with a one to one relationship for that block of optional data

    otherwise a separate table for each group is rarely justified, primarily it becomes an admin headache (not just for the DBA but also the developer (you are having to create new tables for each URL, you have to find all those tables).
    do comparisons between your data becomes awkward, eg if someonme wants a 'top ten report',
    you are loosing the power of the SQL engine to refine, sort and analyse your data

    So in short all similar data in the same table, if you start threatening to blow MySQL's limits then you need to rethink your strategy or possibly rethink your server
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jan 2007
    Posts
    2
    Hi: healdem, r937

    Thanks for the responses. In thinking about the design, I was more worried about the speed of queries/affect on the server that having 1 big table vs. multiple smaller tables would create.

    Food for thought. Anyone else like to chime in?

    If not, thanks both!

  5. #5
    Join Date
    Mar 2004
    Posts
    480
    what is one big table? properly indexed, with database properly tuned, mysql can handle in the 10s and 100s of millions of rows without much effort. Do you expect your table to be that big?

    Lets say you expect your data to be smaller. Say 100,000 rows in one table or 10,000 in each of 10. When you need data from all 10 of those tables, for stats purposes say, you will need to join or union all 10 tables, significantly slower than searching the one table.

    You or others will be hard pressed to come up with good reasons to split the data.

  6. #6
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Size is hardly ever a consideration for splitting up tables. Maybe when you've got an enormous amount of data (millions of rows) and 99% of your queries will return rows from the same small subset of the data. For example when you've got a big table with orders you might want to archive orders that have been closed more than two years ago in a history table.

    The usual reason to have multiple tables is to avoid redundancy of data. If you want to know about that, look up normalization.

Posting Permissions

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