C#,Architecture,Backend Architecture
Modeling a Relational Bookstore Domain with Entity Framework Core
Created the relational data foundation for a bookstore Web API by mapping authors, books, publishers, stores, sales, users, roles, jobs, and refresh tokens with Entity Framework Core.
The Problem
A bookstore domain contains several interconnected concepts, including many-to-many authorship, publisher ownership, store sales, employee roles, and refresh-token relationships.
The immediate goal was to bring that relational structure into an ASP.NET Core project accurately before building resource endpoints and business workflows on top of it.
TL;DR
- Problem: A multi-table SQL Server schema needed an accurate application-side representation
- Solution: Scaffolded and reviewed EF Core entities, keys, navigation properties, defaults, and constraints
- Impact: Established the data layer needed for future bookstore API operations
Technical Leadership
- Kept the case study scoped to the implemented data foundation rather than claiming unfinished API capabilities.
- Mapped composite keys and many-to-many authorship through the BookAuthor join entity.
- Preserved database-specific types, lengths, defaults, and fixed-width constraints.
- Represented sales, publishing, employment, authorization, and token relationships with navigation properties.
- Identified configuration security as a follow-up concern because development connection details should move out of source code.
What I Did
The project translates a mature relational schema into an explicit .NET domain and persistence model.
BookStoresDbContext exposes ten entity sets and configures their tables, columns, keys, defaults, and relationships through the EF Core fluent API.
- Modeled books, authors, publishers, stores, and sales as connected domain entities.
- Implemented the BookAuthor composite key and both sides of the many-to-many relationship.
- Mapped users to roles, jobs, publishers, and refresh tokens.
- Preserved SQL money, datetime, fixed-length, maximum-length, and default-value behavior.
- Configured the ASP.NET Core project with controllers and Swagger as a foundation for later endpoints.
Testing and Validation
- Reviewed entity relationships in both directions through navigation properties.
- Verified primary, foreign, and composite keys match the schema.
- Checked nullable reference types against optional database columns.
- Confirmed database-specific value types and length constraints are represented.
- Identified configuration and endpoint implementation as explicit next phases.
Outcome
- Created a strongly typed EF Core model spanning ten related entity sets.
- Captured complex bookstore relationships without flattening the underlying schema.
- Prepared the project for repository, service, authentication, and controller layers.
- Documented the implementation honestly as a data-model foundation rather than a completed API.