I have a Currency Rate table with a few fields like so:
Currency_Definition__c : M-D(Currency Definition) As_of__c : Date FX_USD__c : Decimal Key__c : String(11) ExternalId
This table gets updated daily with new rates for a couple dozen currencies. The Key is a combination of the Currency ISO code (from the parent object) and the date, e.g., GBP20170131, and is used as a unique key for upserting rates, and for locating rates quickly from other triggers. The Key__c field is automatically populated by the batch process that creates these rates each day. It will be unique as we upsert all records on that value, although at present the field is not declared unique, only ExternalId.
We are now planning to expand this object by adding a Rate_Type__c picklist, with two values: Daily and Spot. All of the existing records are Daily rates. Spot rates will be special, per-transaction rates, created and managed by the system. By storing both types of rates in one table, existing objects can link to either type of rate, and existing formulas and code will just work.
I don't need a Key value for these Spot rates. They will be created (and occasionally deleted) automatically by a trigger, and there's no need to query as there is for Daily Rates - if you need a spot rate, your object will already link to it, thanks to the trigger. But, this table could potentially grow to contain many records, and I will continue to query the table for Daily Rates via the Key__c external Id field. I'm concerned that many Spot rates could lead to enough nulls in that field to cause "Non-selective Query" or similar issues. I should not ever need to query for
Key__c = null.
The question: Is this a valid concern, and should I create Key values for these records to avoid all of the nulls in the Key__c external id field? My simplest solution would be to expand Key__c to 18 characters, and for Spot records assign the ID of the related object to the key. But if it's not needed, I'd rather not add all of those values to the index.
Update with Clarifications, 14 Feb 2017: Upon further reflection, my plan to use the ID of a related object as a throwaway key is flawed if I want it to be unique; there could be cases where a related object has multiple multicurrency relationships each with its own spot rate. I'm sure I can come up with something to generate a unique key value, but not sure I should. To clarify:
String(11) External IDbut is not
- Daily Rates will always have a Unique key value derived from the data, needed for upserts and queries. Daily rates are managed by a Batch and won't be edited by users.
- Spot rates don't need a key, they are always linked (lookup) from the related record(s). The values are sourced from related records, but all Spot rates are managed by trigger and will not be edited by users.
- If I add a key value for Spot records, I cannot use the same type of key (ISO+Date); that won't be unique for Spots. Expanding the key to differentiate Daily v. Spot doesn't solve the problem.
- The only reason I'm considering assigning key values to Spot rates is to avoid having many nulls in
Key__c, potentially leading to "Non-selective Query" or similar issues, but I'm not sure this is a valid concern or reason.
- I will only query by
Key__c = :valueif
valueis non-null (i.e., only for Daily rates).
- Adding unique key values for Spot rates is somewhat non-trivial, but I can do it if there's a risk of query errors should I not do it.
- I could generate non-unique key values for spot rates more easily, and there would not be much overlap; most values would be unique, some values might occur 2 or 3 times.
- I don't know if there is particular value in making
Requiredfield, specifically in light of the above points.