Is there a limit to how many tables you can have in a database?

Is there 开发者_Go百科a limit to how many tables you can have in a database? Would this be considered bad programming or whatever? I have a lot of user information and I'm wondering if it would be ok to have many tables?

If you're considering this question, you should probably change the way you are planning to store data. It is generally considered a bad practice to have a database schema where the number of tables grows over time. A better way to store data is to have identifying columns (like a User ID) and use a row for each user. You can have multiple tables for different types of data, but shouldn't have tables for each user.

No, mysql does not have a limit to number of tables in a database, although obviously you'll be constrained by how much disk space you have available.

That said, if you're asking this question, your prospective design is probably fairly ugly.

Just found this

http://bobfield.blogspot.com/2006/03/million-tables.html

So if you suspect you will have more than one million tables, you should consider redesigning the database ;) Also note, that this blogpost is from 2006.

not usually a logical limit no. but this question begs the discussion - why would you think you might approach a limit? if you will be creating many many tables, then this feels like maybe you really want to be creating many many rows instead... perhaps you could elaborate on your idea so we could provide some schema guidance..?

Generally the limit, if there is one, should be large enough not to worry about. If you find yourself worrying about it, you have larger problems. For instance if you were dealing with customers who have orders, you would create a table for customers and a table for orders. You should not be creating a table for each customer.

Yep, there is a limit.. but you are likely to find it. 65,000 last I heard.. http://forums.mysql.com/read.php?32,100653,100653

I can see a reason some might want a table per user. If each user is going to have an increasing number of logs/entries/rows over time, and you do not want the code to have to sort through a gigantic list of entries looking for rows matching only the particular userID, then the application would simply look for a table with the given userID, and then everything in that table is for that user only. It would improve performance when wanting to compare and sort data for one particular user. I have used this method, all be it with less than one hundred users though. Not sure of any consequences that may be faced with thousands of users.

Why put users in their own tables? Seems like a waste of time to me. One users table with an identifying ID that increases every time a new row is added would work fine.

The ID could be a foreign key for other tables, such as "Blog_Posts" - each blog post would need to have an author, and so you could use an "AuthorID" column which would correlate to a User ID in your users table.

Saves space and time - plus it's cleaner.