Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2009
    Posts
    3

    Unanswered: long table or many tables?

    Hi all,
    I am developing a web application using mysql. I want to make sure I go down the right track here.

    My web-app revolves around people creating lists and then working with the data in those lists. Each list will typically be between 200 and 2000 rows, and each user will typically create 1-50 lists. All of the lists will share the same structure, something like: [int | varchar(50) | varchar(50) | text]

    I'm wandering if it is better to
    1) create a new table for each list. OR
    2) create a very long table for all of the lists' data, and add a listID field to each row specifying which list the entry belongs to.

    What are the pros and cons of having lots of tables, versus having one very long table.

    Thanks in advance for your help!

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    One table will be far better. It doesn't matter much how much data is in it as proper indexes will ensure it's fast to access. It will be much easier to write code using a single table and much easier to manage. I can't think of any advantages of having just one table - perhaps it's easier to set user permissions but in reality this will be handled by the web front end.

    Are you sure a user will create up to 50 lists containing 2000 records? that's 100,000 records - even my wife doesn't give me lists like that. What does the system do?

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by OllieSA
    2) create a very long table for all of the lists' data
    long? as in vertically long (many rows) or horizontally long (many columns)?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Mar 2009
    Posts
    3
    Thanks for the replies. By long I meant vertically long, the table would only need 4 or 5 columns. My list sizes are certainly worst-cases, but I'm guessing that doesn't really matter here. (The application is for an online learning tool so the lists might be vocabulary lists for example.)

  5. #5
    Join Date
    Feb 2009
    Location
    Rutherford, NJ
    Posts
    9
    Yes, always use fewer tables where possible. You should never have two tables storing similar data, it's much more difficult to work with, and an indexed table will provide fast access to millions of rows. One suggestion would be...if you have two fields 'listid' and 'itemid' representing which list it is and which item in it, make that your primary key ('listid','itemid') and set the table to innodb, so it keeps the data stored together on disk in that order. I do not believe MyISAM has this feature.
    And when the new MySQL is finally stable, you can use table partitioning, if you have a lot of disk access due to lack of memory.

  6. #6
    Join Date
    Mar 2009
    Posts
    3
    Thanks for the advice. I've implemented the db using a single table and I'll look into coupling the listId and itemID as you suggest. But in the mean time all is working and my site is up and running at www.vocaber.com.

Posting Permissions

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