How to Set Interdependencies Between Columns

Discussion of open issues, suggestions and bugs regarding database management and administration tools for MySQL
Post Reply
iana
Posts: 2
Joined: Wed 24 Oct 2018 20:54

How to Set Interdependencies Between Columns

Post by iana » 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:

Code: Select all

Table WORKFLOW(
  ID - PK 
  WORKFLOW_TYPE_ID - FK
  OBJECT_TYPE_ID - FK
  OBJECT_ID
) 
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.

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
}
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
  1. Set the WORKFLOW_TYPE_ID to be only a defined subset of IDs based on what is stored in the OBJECT_TYPE_ID field
    1. Set WORKFLOW_TYPE_ID to either 1 or 2 when the Object Type is Deposit (id=2)
    2. Set WORKFLOW_TYPE_ID to either 2, 3, or 4 when the Object Type is Investment (id=1)
  2. Set the OBJECT_ID to use an ID from my INVESTMENT table or from my DEPOSIT table depending on the OBECT_TYPE_ID
Are either of those possible and if so how?

alexa
Devart Team
Posts: 2719
Joined: Fri 24 Jun 2011 14:17

Re: How to Set Interdependencies Between Columns

Post by alexa » Fri 26 Oct 2018 10:55

We recommend you to refer to dedicated SQL tutorials or forums in the Internet on this since we provide technical support only on the functionality of our products.

iana
Posts: 2
Joined: Wed 24 Oct 2018 20:54

Re: How to Set Interdependencies Between Columns

Post by iana » Fri 26 Oct 2018 16:57

Hello Alexa,

My question is regarding your product, and more specifically for the product this forum belongs to dbForge for MySQL.
I want to know how to set interdependencies for the data being generated by your product.

alexa
Devart Team
Posts: 2719
Joined: Fri 24 Jun 2011 14:17

Re: How to Set Interdependencies Between Columns

Post by alexa » Tue 30 Oct 2018 09:17

Thank you for the explanation.

Could you please provide us a more complete example so we can investigate it further and the following information:
1. CREATE definitions of the tables along with data.
2. Where is the data taken from?
3. Example of the resulting table with data.

You can send a reply straight to our support system at alexaATdevartDOTcom and supportATdevartDOTcom .

Post Reply