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.:
garden(id, camellia, californa_poppy, azalea) contains a generic garden with 3 flower species that the garden can contain
users(id, username, some_user_infos, garden_id) contains a generic user and it’s garden id.
garden_id value from table
1, and line with id
1 from table
(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?
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.
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.
Yes, you are correct. Your preferred method matches with the fundamental idea of a relational database. Any app design that allows users to create or delete tables is pretty risky. Think of how much extra effort you will need to have in-place (for security reasons) to make sure that a critical table is not deleted or over-written by a new table. Plus, any program that allows users to run DDL commands (like create/drop) increases the chance of a hack or some other destructive/risky outcome.
It is the responsibility of a program is to abstract/hide the structure of the underlying database. Unless of course, you are simply making a database management app. In which case, only an admin should be using it, not an ordinary user.
A lot of health care applications have an option for user defined tables.
These generally have specific schemas and name prefixes so they can be distinguished from the ‘core’ tables.
Some of the reason for this is that various government mandates appear ‘out of the blue’ to collect information on, for instance, smoking, long term unemployment, etc.
Developers can’t anticipate these, so it’s necessary for users to be able to design and activate ‘templates’.
Whether it’s a good idea or not is beside the point: instructions are coming from someone that isn’t concerned about details.