Propositions about the primary key and the count process

Hello,

To begin, thank you for this interesting module, and it's discovery use to count the media usage.
I'm extending it in a custom module because I'm facing some already posted issues from which I don't to use the patch for I don't agree with them.
The current post is another proposition.

Problem/Motivation

In the media_usage table created by the current extension, the columns are:

• mid
• entity_type
• bundle_name
• eid
• langcode
• submodule

I've created some trait to add common methods to the extensions (ParagraphPresistance, BlockContentPersistance...)

First subject: the choice of the PRIMARY KEY

First of all, debugging my module, I've faced the unique kay built with all the columns. This means that we could read twice a record like:

The submodule field is the module meu_* which the storage is processed from.
This way it is processing, the count will be :

$query = \Drupal::database()->query(
      "SELECT COUNT(DISTINCT mu.eid, mu.entity_type, mu.bundle_name) AS usage_count FROM {media_usage} mu WHERE mu.mid = :mid",
      [':mid' => $media->id()]
    );

This count result is 2 while it is the same attachment: same media id (mid) attached to the same node, and the record is duplicated because it's processed with two modules.
The result should be 1.

insert into media_usage values (4480,'node','my_bundle',3789,'fr','meu_nodes'); -- first insertion
insert into media_usage values (4480,'node','my_bundle',3789,'fr','meu_paragraphs'); -- replication from another submodule: no error.

SELECT COUNT(DISTINCT mu.eid, mu.entity_type, mu.bundle_name) AS usage_count FROM media_usage mu WHERE mu.mid = 4480;
-- processed inside media_entity_usage module.

Second subject: the COUNT process

Currently, the count process is
SELECT COUNT(DISTINCT mu.eid, mu.entity_type, mu.bundle_name) AS usage_count FROM media_usage mu WHERE mu.mid = <mid>;
It doesn't take the 'langcode' field into account.

For example, we could face those insertions:

insert into media_usage values (34,'paragraph','the_paragraph_type',6,'en','meu_paragraphs'); -- first insertion
insert into media_usage values (34,'paragraph','the_paragraph_type',6,'fr','meu_paragraphs'); -- same attached entity in another <strong>langcode</strong>

SELECT COUNT(DISTINCT mu.eid, mu.entity_type, mu.bundle_name) AS usage_count FROM media_usage mu WHERE mu.mid = 34;
-- processed inside media_entity_usage module.

If there is no more mid registration on the same fields mu.eid, mu.entity_type, mu.bundle_name, the count returns 1.
But the attachment is on 2 distinct langcodes. We could change the media on one one of the paragraphs, to adapt the suitable document or image on the langcoded paragraph.

Proposed resolutions

On the first subject, on the media_usage table, instead of (mid,entity_type,bundle_name,eid,langcode,submodule), I would propose to update the PRIMARY KEY as (mid,entity_type,bundle_name,eid,langcode) (remove the submodule column)

On the second subject, the counter, I would propose to add the langcode column. The count sql would be:
SELECT COUNT(DISTINCT mu.eid, mu.entity_type, mu.bundle_name,<strong>langcode</strong>) AS usage_count FROM media_usage mu WHERE mu.mid = <mid>;

Data model changes

PRIMARY KEY: (mid,entity_type,bundle_name,eid,langcode)

Thank you by advance for having read this proposition.