How to Set Interdependencies Between Columns
Posted: Thu 25 Oct 2018 00:08
Is there a way to set interdependencies between columns in a table? Meaning that the value of column Y should be populated differently depending on the value of column X.
I have a bunch of examples but here is a simplified one:
workflow_type_id and object_type_id point to reference tables; object_id is a "FK" but the table to which it points depends on the object_type_id.
If I am dealing with Investment Accounts, my OBJECT_ID will contain a FK to the INVESTMENTS table, if it is a Deposit Account, it should contain a FK to my DEPOSITS table.
So what I need to do is
I have a bunch of examples but here is a simplified one:
Code: Select all
Table WORKFLOW(
ID - PK
WORKFLOW_TYPE_ID - FK
OBJECT_TYPE_ID - FK
OBJECT_ID
)
Code: Select all
WORKFLOW_TYPE{
ID: 1, WORKFLOW: Onboarding
ID: 2, WORKFLOW: Review
ID: 3, WORKFLOW: Raise Capital
ID: 4, WORKFLOW: Invest Capital
}
EVENT_TYPE{
ID: 1, OBJECT: Investment Account
ID: 2, OBJECT: Deposit Account
}
So what I need to do is
- Set the WORKFLOW_TYPE_ID to be only a defined subset of IDs based on what is stored in the OBJECT_TYPE_ID field
- Set WORKFLOW_TYPE_ID to either 1 or 2 when the Object Type is Deposit (id=2)
- Set WORKFLOW_TYPE_ID to either 2, 3, or 4 when the Object Type is Investment (id=1)
- Set the OBJECT_ID to use an ID from my INVESTMENT table or from my DEPOSIT table depending on the OBECT_TYPE_ID