0

My understanding is that MySQL is intended to have tables with millions of rows. I am looking for a database system designed to have millions of relational tables. Am I correct in my understanding that the way MySql queries data makes it inefficient for that sort of an implementation? It is for a long-term, user-driven project, so extensibility is a must.

Thanks!

EDIT: Due to the immediately negative reaction, I'll explain myself. "Millions" of tables would be an issue if the project lived to accumulate a strong user base in time. It would implement an edit system similar to that on Stack Overflow; I considered a variety of solutions, and decided the one I liked best was one using a relational table for each offshoot of edits. I assumed there was some database framework designed for that sort of thing. Is this really considered "bad" architecture? Why is it not just an abnormal type of architecture? What is "wrong" with doing something that way?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mr. Lavalamp
  • 1,860
  • 4
  • 17
  • 29
  • This is so far from the norm, I think you'd need to explain why you need so many tables before people can help you. – mbeckish Jun 26 '13 at 01:07
  • 3
    Millions of tables? You must be kidding. If not, there's almost certainly something wrong with your database architecture. – fvu Jun 26 '13 at 01:07
  • What's "wrong" with doing something this way is that it's inefficient. Why not just store the edits in one table? – davis Jun 26 '13 at 01:20
  • Your edit doesn't address why it wouldn't be possible to do this with a few tables. Maybe you can describe your design some more, and people can suggest how to implement it in a standard way? – mbeckish Jun 26 '13 at 01:27
  • To put this in perspective - what you are looking to do is roughly equivalent to defining a class with millions of List properties that each contain a few elements, instead of a few List properties with millions of elements. – mbeckish Jun 26 '13 at 01:29
  • I think I see what you intend to do. I've seen this being done before, it sort of uses the table as part of the data filter which is normally expressed in the `WHERE` clause. And although at first sight that idea is tempting, I've never actually seen it perform properly in the long run (maintenance nightmare, database reeorgs becoming impossible to do, etc). As @mbeckish already stated, more probably than not what you want to do can be done with a handful of tables, in a way that is tried and tested over time, and will save you headaches in the long run. – fvu Jun 26 '13 at 01:30
  • You said it yourself, "abnormal type of architecture". RDBMS are not designed for having "millions of tables." that is why you have "relations." Depending on the DB vendor, I don't even know what some upper limits are. I bet there would be a huge amount of overhead with very little benefit. RDBMSs are highly optimized to handle large numbers of rows in tables with SQL extentions such as partitions, etc. I have never constructed a database with millions of tables, so I don't have any empirical data to say it would be "bad", just many years of real-world experience. Good luck. – OldProgrammer Jun 26 '13 at 01:34
  • 1
    Just give us an example scenario when table B needs to be used instead of table A. I'm afraid that you may end up with a very strange and hard to maintain architecture. You may be approaching the problem wrong way or trying to reinvent something that's already there using RDBMS. – Piotr Gwiazda Jun 26 '13 at 05:50
  • See [this](http://stackoverflow.com/q/9845156/21727) also. – mbeckish Jun 27 '13 at 03:41
  • [One more](http://stackoverflow.com/a/9274485/21727) opinion for reference. – mbeckish Jun 27 '13 at 12:27

1 Answers1

1

You could always look towards a NoSql DB: From: http://nosql-database.org/

"NoSQL DEFINITION: Next Generation Databases mostly addressing some of the points: being non-relational, distributed, open-source and horizontally scalable."

Edit: Scalable is what I was shooting for..

Suggestion: http://www.mongodb.org/

Edit: Interesting idea about data versioning: Ways to implement data versioning in MongoDB

Community
  • 1
  • 1
Tab
  • 1,702
  • 2
  • 19
  • 39
  • 2
    But he said he's looking for millions of relational tables. Which to me seems like a ridiculous request. – davis Jun 26 '13 at 01:23
  • I know but it is possible to relate nosql docs and at the same time fit the bill for having a quadrillion 'tables'. This is really a design issue which is not related to programming at all IMHO. Thanks – Tab Jun 26 '13 at 01:29
  • Thanks for the answer. I'm pondering. – Mr. Lavalamp Jun 26 '13 at 01:39
  • It's an improvement - at least then you'd go from millions of tables to just storing everything in one big table. :) – mbeckish Jun 26 '13 at 01:46