MySQL Database Design – Is It Good to Have Millions of Tables in a Single Database?

database-designeavMySQLperformancequery-performancereplication

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?

Best Answer

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.

Related Question