0

Context

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

Example :

Data model showing a table person linked to a table company and an associative table between person and language.

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.


Question

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.

Thanks

  • Dynamically supplying table or column names is clumsy. You could do it in a Stored Procedure, but it would be better to construct the query via your app language (PHP or ...) – Rick James May 2 at 0:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.