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

[goekay]

Checklist

• I checked other issues already and found no answer
• I checked the documentation and found no answer
• I am running the latest version and the feature i am requesting is not implemented there

Describe the problem you are trying to solve

We are using a mix of PK and natural key references when it comes to cross-referencing between tables. A benefit of referencing natural keys was: They are there, whenever we need to use them. Additional resolution will be necessary with PKs as the men in the middle to arrive at natural keys. This was the primary motivation to leave them as is.

The following tables are subject for this problem:

• connector referencing charge_box_id
• ocpp_tag referencing parent_id_tag
• reservation referencing id_tag
• transaction_start referencing id_tag

Describe the solution you'd like

• connector should reference charge_box_pk
• ocpp_tag should reference ocpp_tag_pk of the same table to express parent id tag
• reservation should reference ocpp_tag_pk
• transaction_start should reference ocpp_tag_pk

Describe alternative solutions or features you've considered

Leave as is: Not a clean solution. Might block developing some features.

Additional context

...

[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.