erDiagram
    ValidPaths {
        int id PK
        string path
        string hash
        int registrationTime
        string deriver
        int narSize
        int ultimate
        string sigs
        string ca
    }
    
    Refs {
        int referrer PK,FK
        int reference PK,FK
    }
    
    DerivationOutputs {
        int drv PK,FK
        string id PK
        int path FK
    }
    
    Invalidations {
        string path PK
        int time
    }

    ValidPaths ||--o{ Refs : "references"
    ValidPaths ||--o{ DerivationOutputs : "outputs"
    ValidPaths ||--o{ DerivationOutputs : "is_output"
    Refs }|--|| ValidPaths : "referenced_by"

Loading

I'll create an ER diagram showing the relationships between the tables in the Nix database.

erDiagram
    ValidPaths {
        int id PK
        string path
        string hash
        int registrationTime
        string deriver
        int narSize
        int ultimate
        string sigs
        string ca
    }
    
    Refs {
        int referrer PK,FK
        int reference PK,FK
    }
    
    DerivationOutputs {
        int drv PK,FK
        string id PK
        int path FK
    }
    
    Invalidations {
        string path PK
        int time
    }

    ValidPaths ||--o{ Refs : "references"
    ValidPaths ||--o{ DerivationOutputs : "outputs"
    ValidPaths ||--o{ DerivationOutputs : "is_output"
    Refs }|--|| ValidPaths : "referenced_by"

Loading

Key points about the relationships shown:

1. ValidPaths is the central table, containing all store paths
2. Refs has a many-to-many relationship with ValidPaths, as each path can reference multiple other paths and be referenced by multiple paths
3. DerivationOutputs connects to ValidPaths twice:
   • Once for the derivation itself (drv field)
   • Once for the output path (path field)
4. Invalidations is standalone as it just tracks removed paths

The diagram shows cardinality where:

• || represents exactly one
• o{ represents zero or many
• }| represents many to one