I've designed a table 'A' in which it's current status is one property that is also one FK to a table 'B' that contains statuses. Somewhere along the application cycle the status of the entities presented in the table 'A' will change, and I'm wondering if I can just update the FK column with the new status, that obviously is also contained in table 'B'.
It seems pretty normal to me, but as I haven't found a single post/article/question in all my searching I'm not sure whether it is acceptable or completely wrong.
As table A has the foreign key to table B you can simply update the value on table A to a new value in table B.
For example, you have
table A: id, name, status_id (foreign key)
table B: id, status_name
in table B you have:
1, "started"
2, "stopped"
3, "suspended"
in table A you have:
1, "application_A", 1
2, "application_B", 1
If you want to stop application_A, simply update the value in column status_id
to 2.
It's the other way round that's difficult: if for some (god forsaken) reason you would have to update the id
from 2 to 4 in the table B for the row where status_name is "stopped" - only then you would have to dig out ON UPDATE CASCADE
or similar measures.