SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2008
    Location
    Idaho
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    The "Lots of tables, or lots of rows" dilemma

    Ok you experienced database people,

    I have emmbarked on an online productivity application using PHP and MySql. I've developed my own lightweight framework, designed a full template for the UI and now I'm just about to jump in and start connectiong the front end with the database.

    Basically this site will have users who each have their own tasks,projects,files and notes. I hope we will get in the 1000+ user range, so I'm going to plan for that. My question is whether I should make a seperate table for each user and keep their tasks,notes, and projects in the table pertaining to them OR keep all the items in one big table and just associate each item by user ID. As of right now this is the option I am going with, it makes more sense to me and is going to be easier to implement. But my only worry is when there gets to be 100,000 rows in this table that searches and stuff will get very slow.

    Each task,note,file,project has the same columns. Each one just has a different label pretty much and is handled differently within the application.

    I will definitely use InnoDB for this one large table, so I don't have table locking slowing it down.

    So any input on this matter would be great! I have been developing with php and mysql for about 5 years now, I'm just still not a database design guru.
    I used to eat all natural foods...
    until I found out most people die of natural causes.

  2. #2
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,576
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    With proper indexing and hardware, 100k and 100ks of rows won't hurt anyone. If it is easier to do it in one table, then run with it.

  3. #3
    SitePoint Enthusiast
    Join Date
    Dec 2008
    Location
    Idaho
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks wwb,

    Once I finish getting stuff hooked up I'll just have to spend some time optimizing queries and everything. I'm a little concerned mostly because the server we are running isn't that great.

    2.0Gghz P4
    768mb ram
    160gb HD

    But it was free! Since it was sitting in my basement... Hopefully by the time it starts getting slow we can just pay for a new server with the revenue we bring in from all those users!
    I used to eat all natural foods...
    until I found out most people die of natural causes.

  4. #4
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,576
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Yeah, that isn't the best. But hardware is the cheapeast of all development problems to solve these days, so I wouldn't get too hung up there.


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
  •