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.
Resolver III would need examples of the data sets (sanitized if not already). But I suspect I would not invent a releationship in this case (Again, I don't know what calculations you are doing). I imagine you would want your relationship to be based on product or service (what ever you are pricing and selling). Then you RELATED/RELATEDTABLE to perform your calculations based on the min/max range columns.
Deepak_Hi 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?- Data-estDog
This 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 ?