Building the Enterprise DAL Foundation: Database Structure and ULIDs

In any enterprise application, the Data Access Layer (DAL) is where performance and maintainability are won or lost. Part two of our series on building a robust DAL with C# and Linq2Db focuses on the essential, non-negotiable foundations: a clean database structure and advanced scaffolding techniques.

Why Database-First is Non-Negotiable

We anchor this project in a database-first philosophy. The database schema is the primary engineering artifact. It must be designed with database optimization in mind, ensuring indexes and constraints are purpose-built for the engine itself. Our code simply becomes a faithful, flexible reflection of this robust schema. This approach provides a clear separation of concerns, ensuring optimal database performance independently of the application logic.

ULIDs: The Key to Sortable, Performant Primary Keys

To ensure optimal performance on append-only tables, we utilize ULIDs (Universal Unique Lexicographically Sortable Identifiers) as the primary key across all entities.

A ULID is a 128-bit identifier where the first 48 bits are a timestamp. This makes a ULID column naturally sortable by creation time. This property is crucial:

• Minimal Fragmentation: New records are naturally written to the end of the table.
• Optimized Reads: Queries for recent records are highly efficient.
• SQLite WITHOUT ROWID: This feature, combined with ULIDs, allows us to remove the internal integer row ID, making the ULID the sole source of identity and order, clustering the table by creation time.

Scaffolding That Works For You

We rely on Linq2Db's scaffolding but introduce a custom Scaffold Interceptor to eliminate manual setup and keep the codebase clean. The goal is to generate code that is 100% database-accurate but also fully prepared for generic service implementation.

The interceptor automates three key tasks:

1. Interface Injection: Automatically adding interfaces like IIdentifiable<Ulid> to generated entity partial classes.
2. Type Mapping: Enforcing the correct mapping from the database's ulid type to the C# Ulid type.
3. Association Naming: Ensuring predictable, consistent property names for entity relationships (e.g., a Post entity will always have a Comments collection).

By using partial classes, we gain the efficiency of auto-generated code while maintaining the freedom to extend and customize the DbCtx and entities without ever modifying the generated files.

This foundation is crucial for the complex cross-cutting concerns we will address next, such as automatic auditing and permissions.

To view the full multi-tenant schema, the code structure, and the logic behind the scaffolding interceptor, see the complete article on the blog.