I have tables in which some fields can be edited by a user through a form. The form is presented as an information sheet about a person : some fields are read-only, others are editable, some of them can be mutli-valued.
In database, it means that those fields can be :
- a textual field in the line of the 'person' in the table 'person'
- a foreign key in the line of the 'person' (one
INTEGER, or more if the key is multiple columns)
- a new line in an associative table between 'person' and another table
I can edit the person so they now have a different status, work in a different company, speak different language(s). That implies knowing for each field what columns or rows I must edit in the database...
As I want to do this generically, I have a table listing all editable fields.
My idea is to put in a table the way to access/edit the value. I first thought about inputting the names of tables and columns containing the data but it does not handle the case when the value is in a (new) line (multi-valued property causing an associative table to be used).
I'm not sure how I can do this without making it unnecessarily complex (if needed it can be moved to application layer but I would be a bit disappointed).
What bothers me most is the fact that rows might have multiple columns as primary key so I don't know how to reference these generically.
How can I store the information telling me, for each field, the way to edit it in my database, without it being too complex ?
I use MySQL 5.7.