#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    44
    Rep Power
    6

    A million tables in a database?


    Hi everyone,

    I am a newbie so kindly excuse if my question seems a bit dumb.

    If I am thinking of developing a database application and I feel that my database should have a million tables - I don't know if there is a limit to the number of tables in a DB and would like to know- would that be a good or a bad thing ?

    SO if that's the thought in my head, would that indicating that I have some bad design ideas going on in my head or would that be ok?

    If having a million tables in a DB is permitted and OK, would that cause performance issues like a slow down while trying to look up a particular table in the DB ?

    Thanks all !
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,122
    Rep Power
    539
    Originally Posted by ajoo
    would that be a good or a bad thing ?
    Definitely a bad thing! Why do you think you need a million tables?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    44
    Rep Power
    6
    Hi, Thanks for the reply. Well certainly not a million tables, but I thought would keep the number high.

    Well I am thinking of an application where I hope I would have a very large number of registered users who would login every day to complete a certain task. So I imagined that as the number of users increased and each had to login everyday, it could result in a whole lot of traffic and if they all accessed the same DB it could cause a whole lot of issues of conflict as there could be a whole lot of them trying to log in there work in every day. Even for as many as 10,000 users who log in daily, that would be a lot of them vying for the same table in the db at any given time of the day resulting in DB access conflicts.

    So I thought I should give each user a table of his own that he could use to record his work.

    So kindly point me in the right direction. Thanks loads for the reply and looking for more help.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4331
    Originally Posted by ajoo
    So kindly point me in the right direction.
    have the users share a table

    properly indexed, the table should perform fine

    a million users is still not a lot

    Comments on this post

    • NotionCommotion agrees : Totally and completely agree!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    44
    Rep Power
    6
    Ok thanks loads for the reply and suggestion.

    So what I gather is that if at any given instance of time, there are say like 10000 (ten thousand) users simultaneously working and sharing a table in a database ( reading from and writing to it), they won't face any problems like delay or conflicts in accessing the database and there would be no loss of data of any user because of these issues( delays or conflicts).

    Is that correct? I am not doubting the authenticity of the answers given, just making doubly sure.

    Another thing I'ld like to ask is that if I still decide to go ahead with the one table per user module( i won't because of the inputs here but just assuming), other than the fact that it is bad design, is there any downside to it against the single table model.

    Thanks to everyone.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    44
    Rep Power
    6
    Hi all !, Thanks for the reply.

    So what I gather from the reply is that if at an instant there are about 10000 (ten thousand) users reading from or writing into a table simultaneously, it will not create any delays or problems such as conflicts etc. and that data of each of the user would get written into the table successfully.

    I would like to mention that for the table that I have in mind, it would mostly have the users save their work in it and so it would mostly be a table into which data is being written and each user would fill in at least 400 rows of say 100 columns each year.

    Finally I would like to ask that if I decided to go ahead with the one table per user design ( which of-course I would not really with so many Gurus advising me against it) then, other than the fact that it is bad design practice, are there any other down sides to it -any other disadvantages, performance issues or any other complications?

    I read more threads on this issues and one of them said that Indexing slows things down for MySql statements that write into the database like create and update. So indexing does not seem to be a good idea for a table that is mostly written into. Also as suggested, I am reading more about sharing the table and would be back with more queries.

    Thanks all !
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,122
    Rep Power
    539
    Originally Posted by ajoo
    So what I gather from the reply is that if at an instant there are about 10000 (ten thousand) users reading from or writing into a table simultaneously, it will not create any delays or problems such as conflicts etc. and that data of each of the user would get written into the table successfully.
    If you have 10,000 simultaneous users, you have no problems! Take some of your loot, and hire r937 to figure it out for you. I know you can implement transactions and other measures, however, they probably make things go a bit slower. My advise is not to worry about it for now.

    Originally Posted by ajoo
    I would like to mention that for the table that I have in mind, it would mostly have the users save their work in it and so it would mostly be a table into which data is being written and each user would fill in at least 400 rows of say 100 columns each year.
    Note sure that this makes sense. If the user has a window of work, you will probably save the text in the entire window in a single column of a single record. Maybe I don't understand? Reading about database design might help, and I recommend Database Design: Ryan Stephens, Ronald Plew: 0752063317587: Amazon.com: Books.

    Originally Posted by ajoo
    Finally I would like to ask that if I decided to go ahead with the one table per user design ( which of-course I would not really with so many Gurus advising me against it) then, other than the fact that it is bad design practice, are there any other down sides to it -any other disadvantages, performance issues or any other complications?
    All of your listed disadvantages. Performance will degrade as you need your application to manage the tables instead of the database which is designed from the ground up to do so. Maintenance will be a major bear. And since no one does this, I would expect database providers like MySQL don't support that many tables. Bottom line is don't do it.

    Originally Posted by ajoo
    I read more threads on this issues and one of them said that Indexing slows things down for MySql statements that write into the database like create and update. So indexing does not seem to be a good idea for a table that is mostly written into.
    If people are entering data, then indexing is not an issue. The only time where it might be an issue is when a computer application is adding millions of rows at once. Use indexes wisely, and you will be happy.

IMN logo majestic logo threadwatch logo seochat tools logo