I am designing a database to host flexible, evolving models. For example, I may have a users table that contains name, group and password; and I may want to add additional fields such as phone number, at any time. The following screenshot shows an example.
The above table describes the model structure, but I also have user objects in my database. How is such a database typically designed?
In a relational database, you would use ALTER TABLE...ADD COLUMN... when you want to add a new column. You could do this many times over the life of a table. At my company, we have made over 75,000 such alterations, and hundreds more are made every week.
In MySQL 8.0, they now support "instant add column," so it doesn't take a long time. See https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-instant-add-column/ for details.
For some attributes, it may be better to create another table. For example, in your example of adding phone numbers, it is typical for a given user to have multiple phone numbers. This is a multi-valued attribute of a user, so for the sake of database normalization, you should create another table, where you store one phone number per row, with a reference to the user who owns that phone number. Adding a new table that references the user table does not lock or change the user table.
Some people want to add attributes without using ALTER TABLE at all. In a relational database, you would have to use a "semi-structured" data type, like JSON or XML. But SQL semantics don't really know about the virtual fields and structure within such a document, so don't expect it to work as well as real columns. See my presentation How to Use JSON in MySQL Wrong.
NoSQL databases offer different solutions for "fluid" structure of data. Document databases like MongoDB store an object of related data as a "document" which is basically JSON. Key/value databases like ScyllaDB store a record whose primary access method is like a primary key, and the payload in that record can have fixed or fluid fields.
There are always tradeoffs. By making the database more flexible, the datastore has to sacrifice efficiency or integrity. A more flexible data model also makes the client application more complex, because you can't be sure any data record will have a predictable set of fields. So you must write lots of application code to adapt to records with different fields.
You can use the JSON data type for these "optional" fields.
