1

I have designed new MySQL database for my project, In my project I need to create tables dynamically with dynamic columns. So I want to know is their any impact on database performance if I have unlimited (Millions or Billions) tables in a single database. And also want to know what are the max tables MySQL allow to create in a single database (As I can check InnoDB permits 4 Million tables, but I don't know "what is InnoDB?")

ParentTable (Static)
+--------------------------------------------------+
| ID | UserId | ParentName | Desciption | Status |
+--------------------------------------------------+

ChildTable1 (Dynamic)
+---------------------+
| ID | UserId | Value |
+---------------------+

ChildTable2 (Dynamic)
+--------------------------------------+
| ID | UserId | Name| Address| Pincode |
+--------------------------------------+

Above is just a example how I have designed my database with dynamic tables, I'm storing Parents details in a static table and to store child data I'm creating tables dynamically with one or more dynamic columns. Is there any other best way to achieve the same?

Update 1 :

I'm working on a project in which user can create their own web services from UI by simply selecting number of columns and defining name to each column. For now I'm considering, I'll create one table for each web service with reference of UserId (As shown in above example). So in this approach if I have 1 Million users and if every user will create 10 web services then it will create 10 Million tables in database.

How I can resolve this problem?

1

Absolutely not.

Each table takes at least one file in the directory that represents the database. The OS overhead is non-trivial. The OS effort to find a single file (table) in a million-file directory is not pleasant. Etc.

If it generally poor design to be dynamically creating tables; it is terrible design to be creating millions.

Millions, even billions, of rows in a table -- this is OK. Having an extra column that replaces your "table" designation -- this is the "right" alternative.

If you would like to discuss your application further (and more specifically than "parent" and "column"), we can discuss other design considerations.

Same arguments apply to having millions of databases.

  • 3
    This answer is sound advice. But it does not address the question of what motivated the use of dynamic tables. If each of these tables can have its own set of columns, then putting all the data into one big table will be well nigh impossible. I'm not saying that millions of tables is a good idea, but just that this issue may have to be addressed. – Walter Mitty Apr 8 '17 at 21:42
  • 1
    Thanks @RickJames, but it doesn't resolve my problem and I'm completely agree with WalterMitty .. – Shri Apr 9 '17 at 11:08
  • @Rick James, I have updated my question for better understanding. – Shri Apr 9 '17 at 11:34
  • 1
    For discussion of varying set of columns, read several of the Q&A tagged "eav". (I added that tag.) – Rick James Apr 9 '17 at 15:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.