cascade or restrict on delete entities?

[s8sato]

Currently, domains, accounts, and assets are cascaded on unregister due to nested structure, but for shallow objects #3921 we might choose either of the following unregister policies:

1. autoremove orphans (ON DELETE CASCADE)

• entity should have a set of references (IDs) of other entities

2. do not unregister as long as referenced (ON DELETE RESTRICT)

• entity should have a reference counter that indicates how many other entities are referencing it

[s8sato]

• domains, accounts, and assets would keep cascading if that is implied in CanUnregisterDomain and CanUnregisterAccount
• should an account be dominant for the triggers it has registered?
  • related to identify trigger as an account? #4670

[s8sato]

To consider entity relationships, I drew what if the storages under the world were tables of relational database:

erDiagram
    %% parent tables
    DOMAINS {
        _ id PK
        _ logo
        _ metadata
        _ admin FK
    }
    ACCOUNTS {
        _ id PK
        _ metadata
    }
    ASSETS {
        _ id PK
        _ total_quantity
        _ is_mintable
        _ logo
        _ metadata
        _ admin FK
    }
    %% child tables
    DOMAIN_ACCOUNTS {
        _ domain_id PK, FK
        _ account_id PK, FK
    }
    DOMAIN_ASSETS {
        _ domain_id PK, FK
        _ asset_id PK, FK
    }
    ASSET_ACCOUNTS_NUMERIC {
        _ asset_id PK, FK
        _ account_id PK, FK
        Numeric balance
    }
    ASSET_ACCOUNTS_STORE {
        _ asset_id PK, FK
        _ account_id PK, FK
        Metadata datastore
    }
    %% relationships
    DOMAINS ||--o{ DOMAIN_ACCOUNTS : has
    DOMAINS ||--o{ DOMAIN_ASSETS : has
    ACCOUNTS ||--o{ DOMAINS : administrates
    ACCOUNTS ||--o{ ASSETS : administrates
    ACCOUNTS ||--o{ DOMAIN_ACCOUNTS : has
    ACCOUNTS ||--o{ ASSET_ACCOUNTS_NUMERIC : has
    ACCOUNTS ||--o{ ASSET_ACCOUNTS_STORE : has
    ASSETS ||--o{ DOMAIN_ASSETS : serves
    ASSETS ||--o{ ASSET_ACCOUNTS_NUMERIC : serves
    ASSETS ||--o{ ASSET_ACCOUNTS_STORE : serves

Loading

erDiagram
    %% parent tables
    ACCOUNTS {
        _ id PK
        _ metadata
    }
    PERMISSIONS {
        _ id PK
    }
    ROLES {
        _ id PK
    }
    %% child tables
    ACCOUNT_PERMISSIONS {
        _ account_id PK, FK
        _ permission_id PK, FK
    }
    ACCOUNT_ROLES {
        _ account_id PK, FK
        _ role_id PK, FK
    }
    ROLE_PERMISSIONS {
        _ role_id PK, FK
        _ permission_id PK, FK
    }
    %% relationships
    ACCOUNTS ||--o{ ACCOUNT_PERMISSIONS : has
    ACCOUNTS ||--o{ ACCOUNT_ROLES : has
    PERMISSIONS ||--o{ ACCOUNT_PERMISSIONS : serves
    PERMISSIONS ||--o{ ROLE_PERMISSIONS : serves
    ROLES ||--o{ ROLE_PERMISSIONS : has
    ROLES ||--o{ ACCOUNT_ROLES : serves

Loading

where:

• if table P has table C, entity c in C that references p is also deleted when entity p in P is deleted (CASCADE)
• if table P serves table C, entity p in P cannot be deleted as long as any entity c in C references p (RESTRICT)
• P serves C when P administrates C

[Erigara]

Interesting, when you visualized it this way i noticed that either removal of account or domain can have cascade a lot.

Like:

1. removal of domain owner would delete it's domain
2. which in turn delete every account in this domain
3. if any account in this domain was domain owner it trigger domain removal
4. ...
5. Until domain is empty or every account in the domain is not owner of some other domain

[s8sato]

As long as ACCOUNTS serves DOMAINS, as suggested, the removal of the domain owner would be restricted. But yeah, this is exactly the kind of discussion I wanted to have

[s8sato]

updates:

• Fixed DOMAINS:ACCOUNTS from N:N to 1:N
• Fixed DOMAINS:ASSETS from N:N to 1:N
• In response to [suggestion] Eliminate AssetType inconsistencies #4087 (comment), assuming that NUMERIC/STORE assets correspond to FT/NFT respectively, they have been separated as totally different entities:

erDiagram
    %% parent tables
    DOMAINS {
        _ id PK
        _ logo
        _ metadata
        _ admin FK
    }
    ACCOUNTS {
        _ id PK
        _ metadata
        _ domain_id FK
    }
    FASSETS {
        %% fungible assets
        _ id PK
        Numeric total_quantity
        _ is_mintable
        _ logo
        _ metadata
        _ admin FK
        _ domain_id FK
    }
    NFASSETS {
        %% non-fungible assets
        _ id PK
        Metadata datastore
        _ owner FK
        _ domain_id FK
    }
    %% child tables
    FASSET_ACCOUNTS {
        _ asset_id PK, FK
        _ account_id PK, FK
        Numeric balance
    }
    %% relationships
    DOMAINS ||--o{ ACCOUNTS : has
    DOMAINS ||--o{ FASSETS : has
    DOMAINS ||--o{ NFASSETS : has
    ACCOUNTS ||--o{ DOMAINS : administrates
    ACCOUNTS ||--o{ FASSETS : administrates
    ACCOUNTS ||--o{ FASSET_ACCOUNTS : has
    ACCOUNTS ||--o{ NFASSETS : has
    FASSETS ||--o{ FASSET_ACCOUNTS : serves

Loading

where:

• FASSET is an abbreviation for FUNGIBLE ASSET
• NFASSET is an abbreviation for NON FUNGIBLE ASSET
• some additional views would be desired for reference e.g. DOMAIN_ACCOUNTS, DOMAIN_ASSETS