Forum Discussion
Advocate ICrating a custom relationship between two tables
No. A foreign key is a deterministic relationship between records.
You can derive key values from other columns as you are doing. So you could add year and month to your key, if you have the equivilent on your pricing table. But the one record will still only have one value at a time.
Advocate IHi Data-estDog,
This is the pricing table I am using. "Pricing_Key" is the custom column I created by merging the Volume minimum and maximum columns and using "-" as the delimiter.
And this is the sales table I am using:
| Account ID | Company Name | Year | Month | Amount | Amount_Key |
| 12254 | Company A | 2020 | 1 | 5120 | 0-24999 |
| 12537 | Company B | 2020 | 9 | 24878 | 0-24999 |
| 29954 | Company C | 2020 | 7 | 100000 | 24999-249999 |
| 52954 | Company D | 2020 | 11 | 500000 | 500000 - 749999 |
"Amount_Key" is the key I have created based on the amount range cuz I want to charge customers by the amount they belong to. However, the pricing table column's values will not remain the same.
So, the relationship I have created will break in the future. Is there a way to solve this?
Resolver IIThis sound more like a transaction system problem (logic driving pricing decision). The logic for this should be in the source system database and/or associated app.
I say this because if you want, for instance, company A to have a range key of "0-24999" now. But then 6 months from now they increase their volume, and their key would change to (for example) "500000 - 749999". Your probem is that all the old records were generated prices using the old range.
I suppose you could be working in an environment where they want you to hand them a report of customers and last months volume so they know how to input sales into the system. In that case, just make the Year/Month (Period) part of the table unique key, and instead of just adding the range, show the multiplier too. The AmountKey and multiplier should never change cause it is hard wired to the company/account and period.
| Account ID | Company Name | Period | Amount | Amount_Key | Pricing muliplier |
| 12254 | Company A | 202001 | 5120 | 0-24999 | 1 |
| 12537 | Company B | 202009 | 24878 | 0-24999 | .95 |
| 29954 | Company C | 202007 | 100000 | 24999-249999 | .93 |
| 52954 | Company D | 202011 | 500000 | 500000 - 749999 | .91 |
Edit: I noticed after I posted you said that pricing table values will change with time. To me this really scream ETL/ELT logic into your data hub/warehouse/lake. Your pricing table should have effective dates. It is a slowly changing dimension at this point.
- Deepak_
Yes, I was also thinking the same. For making custom relationships at least one column's value should be constant. Still, I was wondering If there is a way to make the key dynamic ?
- Data-estDog
No. A foreign key is a deterministic relationship between records.
You can derive key values from other columns as you are doing. So you could add year and month to your key, if you have the equivilent on your pricing table. But the one record will still only have one value at a time.- Deepak_
Ok, I understand. Thanks for your help Data-estDog.