I'd like to design a database. I have questions and I'll try to explain them the best way.
1) one has told me that if you don't want many details about the project or if you don't want to do many things by hand, go for dynamic idea which means that you don't have a table with columns, but you have a table which has columns as rows. is this a good approach? is it worth it?
2) if you have less tables, it's easier to scale. why is that?
3) Let's say I go for dynamic idea. so what I came up with is that I've created 3 tables. these 3 tables will always be the same and always be helpful for other tables that use dynamic idea. how? here is how.
form_types table
+----+------------+
| id | input_type |
+----+------------+
| 1 | select |
| 2 | input |
| 3 | bool |
+----+------------+
dynamic_columns table
+----+---------------+--------------+
| id | name | form_type_id |
+----+---------------+--------------+
| 1 | year_of_issue | 1 |
+----+---------------+--------------+
type_values table
+----+-------------------+-------+
| id | dynamic_column_id | value |
+----+-------------------+-------+
| 1 | 1 | 1996 |
| 2 | 1 | 2005 |
| 3 | 1 | 2008 |
+----+-------------------+-------+
now I can use these tables to add cars and fetch cars from database dynamically, but to make it work for cars, I'll use these 3 tables and also I had to add another 3 tables for cars.
cars
+----+
| id |
+----+
| 1 |
+----+
column_transport_type
+---------------+-------------------+
| dynamic_column_id | transport_type_id |
+---------------+-------------------+
| 1 | 1 |
+---------------+-------------------+
car_columns_value
+----+--------+-------------------+-------+
| id | car_id | dynamic_column_id | value |
+----+--------+-------------------+-------+
| 1 | 1 | 1 | 1996 |
+----+--------+-------------------+-------+
So let’s talk about the below 3 tables. From user, I get the transport_type_id that is written in column_tranport_type table. Let’s say I got transport_type_id as 1. with 1, I get that dynamic_column_id is equal to 1.. I go to dynamic_columns table and find all that has id equal to 1. so it’s year_of_issue. Then I check if year_of_issue is select or input and if it’s select, I also return select values with the column name “year_of_issue” to front-end so that after returning this information, I for-each loop that information and create the form dynamically. When he enters information into those columns that I dynamically fetched, I use car_columns_value table to put the values there. Do you understand my scenarion?
Is this the great way of how I should do this kind of things dynamically? My worries are that for each kind of things(car or ship) that I want it to work dynamically, I have to create 3 more tables. 3 tables are default and for every dynamic business logic(car,shop, ship) I need another 3 tables and another 3 tables and so on. As you know number of tables will grow and maybe that’s not dynamic so far. I was told that dynamic is good when you shorten the number of tables so that you can scale better. Just give me your ideas. Should I follow my approach or what should I do?
