Blog
Mert Özen's blog posts on software development, backend, frontend, and DevOps.
End to End: Designing the Bookstore Schema from Scratch and a Schema Review Checklist
A good schema is one where the rules in the requirement sentences are written as constraints; most of those rules hide between the sentences. From pre-order, coupon and stock reservation requirements we go to an entity list, a Mermaid ER diagram, a BCNF check with closure and SQL Server DDL, test the constraints against business rules, keep descriptions in the schema, review an EF Core migration and produce a schema review checklist.
Relational or Document? Modeling the Same Bookstore Two Ways
The relational model asks "what is the data?", the document model "how will I access it?"; both need a model, they just draw the boundaries in different places. We build a Bookstore order in MongoDB as a document with embedded lines, show the aggregate boundary, the update anomaly in a document model, the $lookup trap and schema validation, and produce a JSON document from relational data in SQL Server 2025, comparing it with PostgreSQL jsonb.
OLTP vs OLAP Modeling: Dimensional Models, Star Schema and Slowly Changing Dimensions
A normalized OLTP schema is designed for writing, a dimensional model for reading: the grain is declared first, measures go into the fact table and context into denormalized dimensions. In SQL Server we build a star schema from Bookstore sales and show why the same report loses sales on the OLTP schema, how Type 1, 2 and 3 slowly changing dimensions affect past reports when a category changes, double counting from mixed grains, and columnstore.
Data Modeling Anti-Patterns: EAV, Comma-Separated Lists, Repeating Columns and the One True Lookup Table
Modeling anti-patterns are shortcuts that switch off the database's type, uniqueness and foreign key checks; each of them quietly returns a wrong result in some query. In SQL Server we show the row EAV loses and the JSON column alternative, the LIKE trap of comma-separated tags, the rule phone1/phone2/phone3 cannot express, the wrong foreign key of the one big lookup table, leaving every column nullable, and FLOAT for money.
Denormalization: When, How and Who Keeps It Consistent?
Denormalization is a performance decision made with measurements: first try the right index, and if that is not enough, store copied data together with a mechanism that keeps it current. In SQL Server we compare computing a book card's review count and average rating every time, keeping them in books with a trigger, and an indexed view by logical reads, and cover persisted computed columns, summary tables and who keeps copies consistent.
BCNF, 4NF, 5NF and Beyond: When Is 3NF Not Enough?
3NF only looks at non-key attributes, so a table whose columns are all part of a key can be in 3NF and still produce anomalies. BCNF, 4NF and 5NF close these gaps. In SQL Server we show BCNF with a signing day table and bring a lost dependency back with an indexed view, 4NF with languages and genres, the join dependency behind 5NF with a distributor table, and how 6NF relates to temporal data.
1NF, 2NF and 3NF: Normalizing the Order Table Step by Step
Normalization splits a table along its dependencies: 1NF opens lists into rows, 2NF separates partial and 3NF transitive dependencies, and because the determinant becomes the new table's key the join is lossless. In SQL Server we take the order spreadsheet to 1NF with STRING_SPLIT, then to 2NF and 3NF, prove at each step with EXCEPT that no row is missing or spurious, and discuss JSON columns and 1NF and the mapping to the Bookstore schema.
Functional Dependencies and Anomalies: Why Does a Flat Table Break?
A flat table breaks because one row carries several independent facts together; insert, update and delete anomalies are the three faces of this. We load the order spreadsheet into SQL Server and show the three anomalies live, define functional dependencies, determinants and full, partial and transitive dependencies, compute the closure with a T-SQL procedure to find the candidate key, and show why discovering dependencies from data misleads.
Keys and Choosing a Primary Key: Candidate, Natural, Surrogate and Composite Keys
A surrogate key does not replace the natural key, it carries its load: identity lives in IDENTITY or a GUID, and the business rule's uniqueness in a UNIQUE constraint. We cover the chain from superkey to candidate key, the five properties of a good primary key, the traps of ISBN, email and national ID numbers as keys, the rule a composite foreign key catches and its cost, the physical type decision, and PostgreSQL 18's uuidv7() function.
Modeling Relationships: One-to-One, One-to-Many, Many-to-Many, Self-Reference and Weak Entities
Relationships land in tables through rules derived from the model: the 1-N foreign key sits on the many side, M-N is an associative entity with its own identity, and a 1-1 foreign key sits on the optional side as the primary key. In SQL Server we show a link table whose key changes when a role is added, the cycle trap in self-references, a ternary relationship that breaks when split, ON DELETE CASCADE derived from a weak entity, and the two meanings of a nullable foreign key.
ER Modeling: Entities, Attributes, Relationships and Crow's Foot Notation
ER modeling draws data as entities, attributes and relationships; describing a relationship correctly takes both the maximum (cardinality) and the minimum (optionality) at each end. We cover simple, composite, multivalued and derived attributes, unary, binary and ternary relationships, the Chen and Crow's Foot notations, the Bookstore's Mermaid erDiagram draft, and what is lost when you generate a diagram from SQL Server's catalog views.
What Is Data Modeling? From Requirements to Conceptual, Logical and Physical Models
Data modeling means deciding which data you store and under which rules before writing code; if the schema is wrong, no index makes it right. Using the Bookstore, we show the conceptual, logical and physical levels, extracting entities, relationships and rules from requirement sentences, the access pattern list, and why an EF Core schema derived from a screen class turns into a single table full of JSON.