sql-serverdatabase-design

Entity storing it's status as a FK to a status table. Can I update the FK once the status changes?


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.


Solution

  • 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.