Skip to content

Database — Context ERD

The two contexts as overview diagrams. The model holds 361 relationships (325 core, 36 warehouse) — far too many for one picture — so each diagram here shows the spine of one area.

Every entity also has its own ERD in the per-module dictionary, and between them those 161 diagrams cover every relationship in the model.

The two databases

flowchart LR
    subgraph CORE["NKO_DB — NKO_DBEntities1 · 140 entities"]
        direction TB
        A["Identity and permissions<br/>ApplicationUser · AppUser · AppUserGroup · AppPermission"]
        B["HR · Payroll · Recruitment · Training"]
        C["Procurement · Finance"]
        D["Reference data · notifications · archive"]
    end

    subgraph WARE["NKO_DB_Warehouse — WarehouseDBContext · 21 entities"]
        direction TB
        E["Warehouse · CatL1 · Stock · StockSpecification"]
        F["CenterStock · StockDetails · StockReleaseReq · Waybill"]
        G["Asset · AssetDetail · Destruction · GRNReport"]
    end

    C -->|"BGRN.TransfareToWarehouse<br/>application code, no FK"| F
    C -.->|"same hand-off"| G
    WARE -.->|"UserId / CreatedBy as plain int<br/>not a foreign key"| A

There is no database-level link between them. The only write path from core to warehouse is BGRN.TransfareToWarehouse, which inserts CenterStock / StockDetails / Asset rows and flags the core GoodReceivedNote. Warehouse rows reference employees by bare int, so nothing prevents a dangling id.

AppUser — the hub

173 of the 325 core relationships point at AppUser. It is not one table among many; it is the centre the rest of the schema hangs from.

erDiagram
    direction LR
    ApplicationUser ||--o| AppUser : "IdentityUserId"
    AppUser ||--o{ AppUserContract : "employment terms"
    AppUser ||--o| AppUserExtend : "one-to-one, UserId"
    AppUser ||--o{ AppUserAttachment : "documents"
    AppUser ||--o{ AppUserSign : "e-signatures"
    AppUser ||--o{ AppUserFamily : "dependants"
    AppUser ||--o{ AppUserRelative : "conflict of interest"
    AppUser ||--o{ AppUserGroupMember : "group membership"
    AppUser ||--o{ Attendance : "daily attendance"
    AppUser ||--o{ AppUserAllowance : "allowances"
    AppUser ||--o{ AppUserDeduction : "deductions"
    AppUser }o--|| CommonBranch : "BranchId"
    CommonBranch }o--o| AppUser : "6 role pointers"
    AppUser }o--o| CommonSubBranch : "SubBranchId"
    AppUser }o--o| AppUser : "ManagerId"
    AppUser }o--o| AppUser : "TechManagerId"

The two self-references — ManagerId and TechManagerId — are what StaffPermissionchecker walks to decide whose records a manager may see. See Security & Permissions.

The relationship also runs the other way. CommonBranch holds six pointers back to AppUser — the branch's manager, HR, finance, logistics, treasury and accounting holders (AppUser exposes them as CommonBranchManagers, CommonBranchHRs, CommonBranchFinances, CommonBranchLogistics, CommonBranchTreasures, CommonBranchAccountings). These are the named role-holders NextUserHelper routes approvals to — so a missing or stale branch assignment is a common reason a document stalls.

AppUserExtend is one-to-one with AppUser, configured with HasForeignKey<AppUserExtend>(ad => ad.UserId).

AppUser also carries a global query filter (!x.IsDeleted && x.Status == 1), so deleted and draft employees are invisible to every query that does not call IgnoreQueryFilters().

Identity and permissions

Two tables represent one person, joined by AppUser.IdentityUserId:

erDiagram
    ApplicationUser ||--o| AppUser : "IdentityUserId"
    AppUser ||--o{ AppUserGroupMember : "IsEnable"
    AppUserGroupMember }o--|| AppUserGroup : "the role"
    AppUserGroup ||--o{ AppUserGroupPermission : ""
    AppUserGroupPermission }o--|| AppPermission : "PermissionName = the key"

ApplicationUser is the sign-in record (ASP.NET Core Identity); AppUser is the employee. A person's effective permissions are the union of the keys of every enabled group they belong to — there is no fixed role list. Full walk-through in Permissions.

Procurement chain

Each document is a separate table carrying its own approval columns. The chain runs at two levels at once — document to document, and line to line:

erDiagram
    Project ||--o{ PurchaseRequest : "PurchaseRequests"
    PurchaseRequest ||--o{ RFQs : "RFQs"
    PurchaseRequest ||--o{ PurchaseOrder : "PurchaseOrders"
    RFQs ||--o{ RFQMat : "requested lines"
    RFQs ||--o{ RFQProvider : "invited suppliers"
    RFQs ||--o| RFQTechnical : "one-to-one, shared key"
    RFQs ||--o{ CommitteeUser : "evaluation committee"
    Provider ||--o{ RFQProvider : "Provider"
    RFQProvider ||--o{ RFQProviderMats : "quoted prices"
    RFQMat ||--o{ RFQProviderMats : "per requested line"
    RFQProvider ||--o{ RFQProviderJustification : "selection rationale"
    RFQProvider ||--o{ PurchaseOrder : "the winning supplier"
    PurchaseOrder ||--o{ PurchaseOrderMat : "ordered lines"
    PurchaseOrder ||--o{ GoodReceivedNote : "deliveries"
    GoodReceivedNote ||--o{ GoodReceivedNoteMat : "received lines"
    GoodReceivedNote ||--o{ PaymentOrder : "payments"
    PaymentOrder ||--o{ PaymentOrderMat : "paid lines"
    Materials ||--o{ RFQMat : "catalogue item"

At line level each stage points back at the one before it:

flowchart TD
    A["RFQMat — requested line"] --> B["PurchaseOrderMat — ordered"]
    B --> C["GoodReceivedNoteMat — received"]
    C --> D["PaymentOrderMat — paid"]
    B -.->|"ProcBudgetLine"| E["BudgetLine"]

Four things the model makes explicit:

  • A payment order hangs off the GRN, not the purchase order. You pay for what was received. PaymentOrder has no navigation to PurchaseOrder at all.
  • A purchase order points at an RFQProvider — the winning supplier's bid — not at the RFQ directly. It reaches the RFQ through that bid.
  • There is no purchase-request line table. A PR's lines are recorded at RFQ stage as RFQMat.
  • "ProcBudgetLine" is not a table — it is the navigation name on PurchaseOrderMat (and BudgetLinesCategory.ProcBudgetLines) pointing at an ordinary BudgetLine. Budget-line availability is shown on the PR screen but not enforced at write time.

RFQProviderMats is one of the few plural class names in the model. GoodReceivedNote is where the chain crosses into the warehouse — see the hand-off above.

Finance

erDiagram
    direction LR
    Project ||--o{ ProjectDonors : ""
    Donor ||--o{ ProjectDonors : ""
    MainDonor ||--o{ ProjectDonors : ""
    MainDonor ||--o{ DonorPartners : ""
    Donor ||--o{ DonorPartners : ""
    Donor ||--o{ DonorExchangeRate : "per-donor rates"
    Project ||--o{ BudgetLine : "BudgetLines"
    BudgetLinesCategory ||--o{ BudgetLine : "category"
    MasterBudget ||--o{ MasterBudgetDetails : ""
    MasterBudgetDetails ||--o{ MasterBudgetDetailsMonths : "monthly split"
    MasterBudgetDetails ||--o{ BudgetLine : "rolls up to"
    BudgetLine ||--o{ BudgetLinesUser : "staff charged"
    BudgetLine ||--o{ BudgetLineBranch : ""
    BudgetLine ||--o{ BudgetLinePayment : ""
    BudgetLine ||--o{ Materials : "catalogue items"
    Project ||--o{ PartnersContribution : ""
    Donor ||--o{ PartnersContribution : ""
    PartnersContributionMain ||--o{ PartnersContribution : ""
    Project ||--o{ ProjectMonths : ""
    Project ||--o{ ProjectExtensions : ""
    Project ||--o{ ProjectPlace : ""
    Project ||--o{ ProjectProgram : ""
  • Donors and projects are many-to-many, through ProjectDonors, which also records the MainDonor. There is no direct Donor → Project link.
  • MasterBudget is not linked to Project in the model. A budget line reaches the master budget through MasterBudgetDetails, and reaches its project separately through ProjectId.
  • Two exchange-rate tables. DonorExchangeRate holds per-donor rates and navigates to Donor; ExchangeRates holds the global rates and has no navigation to anything.

Project and BudgetLine are the other two entities carrying a global query filter, both on IsDeleted.

Warehouse — configured relationships

The 27 relationships configured in WarehouseDBContext.OnModelCreating, every one OnDelete(NoAction). The model has 9 more that EF infers from naming and attributes — the Computer one-to-one, AssetsReturn, MaintenanceReq and EmpCat1Permission links among them — and 6 of those cascade. They appear in each entity's own diagram in Warehouse.

erDiagram
    direction LR
    CatL1 ||--o{ Stock : "CatL1Id"
    Stock ||--o{ StockSpecification : "StockId"
    Stock ||--o{ CenterStock : "StockId"
    StockSpecification ||--o{ CenterStock : "StockSpecificationId"
    Warehouse ||--o{ CenterStock : "WarehouseId"
    CenterStock ||--o{ StockDetails : "CenterStockId"
    WareArchiveFile ||--o{ StockDetails : "FileId"
    Warehouse ||--o{ WarehouseEvidence : "WarehouseId"
    Warehouse ||--o{ StockReleaseReq : "WarehouseId"
    Warehouse ||--o{ StockReleaseReq : "TransferToWarehouseId"
    CatL1 ||--o{ StockReleaseReq : "CatL1Id"
    StockReleaseReq ||--o{ StockReleaseReqDetail : "SRRId"
    CenterStock ||--o{ StockReleaseReqDetail : "CenterStockId"
    StockSpecification ||--o{ StockReleaseReqDetail : "StockSpecificationId"
    StockReleaseReq ||--o{ Waybill : "SRRId"
    Waybill ||--o{ WaybillDetail : "WayBillId"
    CenterStock ||--o{ WaybillDetail : "CenterStockId"
    StockSpecification ||--o{ WaybillDetail : "StockSpecificationId"
    StockSpecification ||--o{ Asset : "StockSpecificationId"
    Warehouse ||--o{ Asset : "CWarehouse"
    Destruction ||--o{ Asset : "DestructionId"
    Asset ||--o{ AssetDetail : "AssetId"
    Warehouse ||--o{ AssetDetail : "WarehouseId"
    Warehouse ||--o{ GRNReport : "WarehouseId"
    StockSpecification ||--o{ AssetPendingAmount : "StockSpecificationId"
    StockReleaseReq ||--o{ AssetPendingAmount : "SRRId"
    Warehouse ||--o{ AssetPendingAmount : "WarehouseId"

Reading it: CatL1 → Stock → StockSpecification is the catalogue; CenterStock is the per-warehouse holding of one specification; StockDetails is the movement ledger (every entry, release and pending release is a row); StockReleaseReq + …Detail is the release request, and Waybill + …Detail is its dispatch note. Assets branch off StockSpecification and keep their own movement ledger in AssetDetail.

Warehouse is the warehouse-side hub with 8 dependents, mirroring AppUser's role in the core.

One-to-one relationships, and one entity with no configuration at all

The diagram above shows the 27 one-to-many relationships. The warehouse also configures one one-to-one:

x.Entity<Computer>()
    .HasOne(p => p.Asset)
    .WithOne(p => p.Computer)
    .HasForeignKey<Computer>(p => p.ComputerId);   // shared primary key

Computer extends an Asset with machine-specific columns, keyed on the same value — ComputerId is both its primary key and the foreign key to Asset. BAsset.CreateAsset creates the row only when the specification is the computer type.

It is one of three one-to-ones in the model. The other two are in the core context: AppUserExtend ↔ AppUser (fluent, HasForeignKey<AppUserExtend>(ad => ad.UserId)) and RFQTechnical ↔ RFQs (a [ForeignKey("RFQ")] placed directly on RFQTechnicalId, making it a shared key).

AssetsReturn is the one warehouse entity with no configuration beyond its DbSet — no HasKey, no relationship. It maps entirely by convention. See Warehouse.

Attendance — the one index

The only index configured in either context:

modelBuilder.Entity<Attendance>()
    .HasIndex(p => new { p.UserId, p.Year, p.Month, p.Day })
    .IsUnique(true);

It enforces one attendance row per employee per day — the only uniqueness constraint expressed in the model beyond primary keys. Tracked as R2-2.