What is a good way to add extra info to data-entries from a website front-end?

Question

I want users to be able to dynamically add 'columns' from the front-end of the website. I understand that it is probably not best practice to actually add columns to a table from the front-end, so I was looking for a better way to handle this.

The use case:

I am making an app with a determination table. The user can fill out details of the animal/plant (for example leaf shape) and is supposed to end at the right species.

I want to make it future proof, so that if someone fills out all details and the species they have is not the one the table comes up with, the user can add both their species, and the detail that would tell both species apart.

For example: if the user found a daisy but the table comes up with dandelion, the user could add the daisy and add 'petal colour' as a distinquishing feature.

Users should then be able to fill out the petal colour for all plants that were already in the database.

My database at the moment:

I have one table where all details (like species name, leaf shape etc.) are stored in columns.

My webiste: I use angular 7 for the front-end, PHP on the server and a MySQL database, but general answers are also very welcome.

Answer 1

You don't need to add columns for this - that would, as you say, not be ideal.

This can be done with a general attribute / value lookup tables - an attribute table (containing Petal Color) and an associated attribute value table (blue,white) are kept for each color, along with a UI for entering these.

attribute       value
===========     =====
<petal color>   white
<petal color>   blue

Your attributes for a particular species should not be kept in the species table, but in an intersection table that maps all attributes for a particular species

species  attribute     value
=======  =========     =====
<daisy>  <petal color> <white>

(Note that should be foreign keys to the values in the primary object tables and not the actual values)

This would allow you to add new attributes through a UI, and add those attributes to a species.