Ensure global uniqueness of Tariff.product + what?

[forsberg]

With #9 closed, Tariff.product got a more clear role, which is good.

In our use case, we will have to store which tariff each of our customers is using, and hence, we need some kind of unique ID to be able to connect the customer with a tariff. Using Tariff.product makes sense at it will help with the lifecycle handling.

However, as Tariff.product is only unique on a per company level, how do we ensure we have a unique key for company + Tariff.product in our database? Or in very concrete terms - what combination of keys should we use in an SQL database to form our uniqeness index?

• Using Tariff.companyName would be one option, but companies tend to change name from time to time, so I don't see this as a good option.
• Using Tariff.companyOrgNo might be a better option, but.. are we sure this will work on an European level? One idea might be to force it to be a European VAT identification number instead of a swedish organization number.
• I could use the URL of the API, but that might also change. It's a bit clumsy, and one API might include tariffs from multiple companies (See Tekniska Verken API, contains data from three companies). API URL + companyOrgNo + product might be unique, but again, a bit clumsy.
• Other ideas would be to always prefix the Tariff.product with a UUID. Or have a separate field which is a UUID for the product. This would be globally unique.

Would be great if the specification was more clear on this.

[larwa99]

Isn't Tariff.id what you need? It is a unique identifier (UUID) for the tariff definition. The Tariff.id will change if the tariff definition is updated in any part (validity period, prices, typo correction etc.) and then you can use Tariff.product to locate the updated definition and the new Tariff.id.

[forsberg]

Not quite. The thing I'd like to do is to associate a Tariff.product with each of my customers, as that is what's persistent over time.

Which tariff to actually use at any particular point in time will depend on validity time of each tariff, so that will be looked up based on the Tariff.product and validity time of Tariffs available for that particular Tariff.product, or stored by updating some secondary SQL table with mapping between Tariff.product and the tariff in use when new tariff IDs are detected (i.e daily import).

As we have customers from all of Sweden, and we can hope this API becomes a standard for Europe, just trusting that Tariff.product is globally unique given its current specification won't do, so we need to base this mapping between customer and product either on a combination of some other data + Tariff.product or Tariff.product should be made globally unique, for example by prefixing it with a UUID.

Hope that explains the problem I'm seeing?

[larwa99]

Thanks for elaborating, it is more clear now and it is the "DSO" + Tariff.product that will make it unique. Personally I would probably use an internally generated DSO key to ensure uniqueness rather than depending on a property in the API. I assume you anyway have some kind of database entry for each DSO with the base URL etc.? If you don't want to depend on any local data the Tariff.companyOrgNo is your best bet at this point.

I'll ask around if there is some existing DSO identifier that is commonly used across EU that would make sense to add to Tariff.

[forsberg]

The problem can definitely be solved, I'm mostly trying to come up with a model where as much as possible in my identification of tariffs is as close to the API and terms used in the industry as possible, to avoid confusion.

If there's some DSO identifier that can be used that would make a lot of sense, not only here but also in the response from the suggested enumeration API (#37). Then I could populate your suggested database table directly from the enumeration API.

[mattiaspalmqvist]

I agree, having a unique DSO identifier (throughout europe) would be a nice solution. I don't know if there is such a register. But until we have answer on that question I believe that using Tariff.companyOrgNo + Tariff.product would solve the problem.

I think that the Tariff.companyOrgNo property should be the unique identifier for the DSO. Then we might change the name of that parameter if there are a better EU standard name/number. But the function of the property will remain the same, a DSO unique identifier.

I can add that to the description of the field for the next release.

[mattiaspalmqvist]

What needs to be investigated is if there are some European unique DSO identifier to be set in the companyOrgNo property (or replace it with "dsoIdentifier" etc...)