Blog
Mert Özen's blog posts on software development, backend, frontend, and DevOps.
Hierarchical Data in SQL: Adjacency List, Recursive CTE, Closure Table and hierarchyid
When subtree queries on a department tree are frequent and moves are rare, SQL Server's hierarchyid (with LINQ support since EF Core 8) plus a ParentId for FK integrity is a good choice; use a closure table if you need database independence. Walking the tree node by node in the application causes N+1, and cycles should be blocked at move time with IsDescendantOf.
When the Status Column Is Updated from Everywhere: Transition Tables, State Machines and Status History
Stop treating the status column as a field any endpoint can write: let the client send an action instead of a status, keep allowed transitions in one place (a transition table, domain methods or Stateless), write every transition with its reason to a status history in the same transaction, and use a rowversion to stop double approvals.
Temporal Data: Effective-Dated Records, Price History and the Bitemporal Model
Data with retroactive corrections needs two time axes: valid time (which date the fact is true for) and transaction time (when it was recorded). A SQL Server temporal table only gives transaction time; make a table with half-open [ValidFrom, ValidTo) intervals system-versioned and you can write bitemporal queries with EF Core 10.
Audit Trail: Who Changed What, When and Why?
For a reliable audit trail, write changes with a SaveChangesInterceptor as per-property old/new values plus user, correlation id and reason, in the same transaction as the business data; mask personal data and make the table append-only. Cover paths that bypass the interceptor, such as ExecuteUpdate, with summary entries and a temporal table safety net.
Doing Soft Delete Right: IsDeleted, DeletedAt, Filtered Unique Indexes and Archive Tables
To do soft delete right, filter unique indexes with WHERE DeletedAt IS NULL, link records deleted in the same operation with a DeletionId, and answer KVKK/GDPR erasure requests with anonymization rather than soft delete. If restore isn't a real requirement, an archive table or hard delete plus audit is the simpler choice.
Enum or Lookup Table? Keeping Reference Data in Code and in the Database
Closed lists that drive code behavior (order status) should stay enums with a database mirror seeded via HasData and linked by an FK, while open lists managed by the business (expense type) should live in a lookup table with IsActive and change without a deploy. The post shows the renumbering and string-enum rename traps with real output.
Exposing Ids: int Identity, GUID v7 and the Public Id Split
The most balanced way to expose an id is a bigint identity as the internal clustered key, an immutable unique PublicId outside, and an ownership condition in every query. In our measurement Guid.CreateVersion7 fragmented as badly as v4 in SQL Server (99 percent, 4,987 pages), because SQL Server sorts uniqueidentifier by its last 6 bytes; EF Core's sequential generator stayed close to identity.
Sentinel Values: The Hidden Cost of 0, -1, "1900-01-01" and 9999-12-31
Sentinel values (0 in salary, 9999-12-31 in an end date, 1900-01-01 in a date) silently corrupt aggregates: in the example the average salary comes out as 51,000 instead of 85,000. The fix is usually a nullable column, a separate state field when absence has several meanings and a Special Case type in the domain; a sentinel can be a deliberate standard only when a single producer owns it (temporal tables, a persisted computed column).
The Shadow Record: A Virtual Root That Doesn't Exist in the Database — "Company = 0" Above Departments
A "Company = 0" root that doesn't exist in the database works safely if 0 is turned into a DepartmentScope type (null inside) right at the API boundary; carry 0 as a plain int and you get empty lists on reads and FK violations on writes. The post compares the virtual root, a real root row, a Company entity and an explicit scope contract with their trade-offs.
What Is a Solution Pattern? Real-World Problems Missing from GoF and the Pattern Card Template
A solution pattern is a recurring application decision that, unlike GoF's code-structure patterns, cuts across data, API and UI together (soft delete, sentinel values, a virtual root). This post introduces the series' Pattern Card template, its fixed trade-off criteria and the schema of the HR Portal sample application.