Normalize the relationships
Seven tables separate geographic references, addresses, buyer references, transactions and lots. Foreign keys, constrained measures and indexes document their relationships.
Analytics & ML
Case study / 08
Preserve transaction grain through complex joins.
A normalized transaction model for geographic comparisons, temporal analysis and grain-aware SQL queries.
Technologies & concepts
Technical demonstration / selected source
Private repository
01 / The context
Real-estate transactions link geography, addresses, buyers and multiple property lots. Joining these relationships without preserving transaction grain can multiply values and distort geographic or temporal summaries.
02 / Engineering decisions
Seven tables separate geographic references, addresses, buyer references, transactions and lots. Foreign keys, constrained measures and indexes document their relationships.
Regional and monthly totals are computed before lot-level comparison. Transaction values repeated in the lot-context view are treated as non-additive.
Price-per-area examples use single-lot transactions. Orphan-reference and missing-value queries expose data-fitness questions before interpretation.
03 / System view
Illustrative architecture from the project presentation. It describes the selected design; it does not imply a deployed service or a measured operational result.
View the full-size diagramGeographic reference
Transaction model
Lot relationships
Analytical queries
Quality checks
04 / Selected artifacts
sql/schema.sqlsql/analytics.sqlsql/quality_checks.sql05 / Scope & limitations
Demonstration and source showcase with SQL source but no transaction data or database. Price-per-area examples cover single-lot transactions only; multi-lot allocation needs a separate rule. Minimum sample-size filters are illustrative choices rather than statistical guarantees. No buyer names, real addresses or observed market results are included.
The GitHub repository is private. Its link works for authorized collaborators; other visitors can contact me to discuss the project.
Next case study / 09
The next conversation