Mysql – Is it a good practice to create tables dynamically in a site

best practicesMySQL

A friend asked me to build a site with a few "static" and "dynamic" tables.

In fact he wants to have a few tables which can't be deleted, and some "dynamic" tables which can be created directly from site users, according to their needs.

I.E. if the user needs some "optional" that in the current db doesn't exist, he creates a new table for his specific need.

I think this is not a good way to do it, i think it is better to have a list of all possible optionals in a table and then flag them for each user, i.e.:

Table garden(id, camellia, californa_poppy, azalea) contains a generic garden with 3 flower species that the garden can contain

Table users(id, username, some_user_infos, garden_id) contains a generic user and it's garden id.

If garden_id value from table users is 1, and line with id 1 from table garden is (1, 1, 0, 1) It means that user with garden_id=1 has every allowed flower but California Puppy.

This is my idea.

My friend says it would be better to dinamically create tables and connect them to the user to dinamically define new flowers.

Wouldn't it be better to have every possible flower as a field of the garden table and flag it as i want to do?

Best Answer

Short answer: In general it is no good practice to dynamically create tables and columns. But there can be rare special cases there it is good practice.

Long answer:

You may run (at least) on the following problems with your solution:

  • A user can have only 1 garden, because the garden_id is in the table "users".
  • If you want to add some more infos to a flower, e. g. color or size you need one more column for each flower.
  • You must know all the flower names (column names) to write the sql-query.

If I understand your requirement correctly, this is a case for an intersection table (many to many relation between flowers and gardens).

Maybe this will solve your needs:

Create the following tables:

  • Create a table "Flowers" which contains all possible flowers with columns: "Id", "Flower". You may add additional columns which describe the flowers, e. g. color, size,...
  • Create a table "Users" with colums "Id", "username",... This table contains only the users and no info about his garden or flowers.
  • Create a table "Gardens" with colums "Id", "User_Id". You may add additional columns which describe the garden, e. g. size. This table contains infos about the garden and to which user the garden belongs. But no infos about the flowers in the garden.
  • Create a table "Garden_Flowers" with columns "Id", "Garden_Id", "Flower_Id". This table contains the information, which flowers are in which garden. It may contain additional infos, e. g. when the flowers was planted.

Now you can add new flowers into table "Flowers" without the need to add new colunns or tables. You can define which flowers are in which garden with the intersection table "Garden_Flowers". Just insert one record for each flower with the flower_id and the garden_id.

Related Question