Use PKs for cross-referencing instead of business/natural keys about steve

[juherr]

reservation, transaction, connector and charge_box are part of the CPO domain.
ocpp_tag is part of the EMSP domain.

I think the idea is good for connector and ocpp_tag because it is related to the same domain. But I'm less sure for reservation and transaction_start because of the difference of domain and data ownership.

IMO, CPO domain should not have a strong dependency to the emsp domain and using the id_tag as FK allows to keep the expected data at the good place without breaking the current implementation.
From a design point of view, removing the relation won't be a good deal if needed a day.

[goekay]

i think one potential problem is the following: since ocpp tags are not guaranteed to be universally unique, what if multiple EMSPs use the same value and therefore are in conflict with each other? using a natural key is a problem here. a transaction or reservation is not guaranteed to be in a universally unique relation with an ocpp tag id.

the same can be said for charge box id.

therefore, in general: natural keys are not guaranteed to be unique across different providers/worlds.

[goekay]

moreover, current db models of reservation and transaction require a valid ocpp tag id FK, meaning: an entry has to exist in ocpp_tag table. if the entry has to exist anyways, this entry will have a PK. therefore, changing the reference from natural key to PK is just syntactical.

[juherr]

If I understand well you say we need more information because the tag is insufficient alone, especially when there are many actors.
I have a potentially different approach here because I think the CPMS should first store events from the chargers and then reconcile data. Replacing natural keys for transactions and reservations will maybe complicate the FK removal.

[goekay]

Replacing natural keys for transactions and reservations will maybe complicate the FK removal.

FK removal? what would be the motivation and use case? do you have a fork of steve where you made some custom enhancements/changes which would be broken after making the changes of this feature request?

[juherr]

There appears to be a discrepancy between the intended functionality of the charge/reservation tag, as seen from a CPO perspective, and the actual manageability of tags through the interface, as seen from an EMP perspective.

This mixing of concepts is potentially problematic and could result in functional issues.

However, it is important to note that this discussion is based on conceptual arguments rather than concrete instances in a production environment.