Designing a Database from Barely-There Requirements
Like many ideas born in Kathmandu, this one emerged from a tea shop: two of my friends discussed a room-rental app to bridge a market gap for a direct-to-owner platform eliminating brokers, a concept surprisingly absent in Nepal's popular app scene.
I am currently learning Data Engineering and was genuinely interested in finding out what might go in the backend of such an application. What kind of data is involved? What tables would be needed? This post explains my process of translating a few bullet points to a fully-fledged database schema, offering insights into how to approach database design when the requirements are barely there.
The Initial Requirements
This was the original brief I received:
The initial requirements expressed a desire to build an application, but in terms of actual details, they were almost non-existent. I urged my friends to go a bit deeper into each requirement, but there was no follow-up. So, I took it as a chance to build something realistic, grounded in what I had seen in the real world.
Refining the Requirement
The first step was research. The initial document referenced "NoBroker," a popular Indian app. Given that rental practices are similar in India and Nepal, I used it as a starting point. I also explored local apps like Basobas, which had clearer classifications and were more relevant to the Nepali market.
From the research, I wrote my own detailed requirements. After laying down the requirements in detail, the design became a lot easier. The core entities and their attributes began to emerge naturally.
Here is an example of one of my requirements from the perspective of a tenant:
You can view the full requirements here.
Building a Conceptual Data Model
With better-defined requirements, I began creating a Conceptual Data Model. This included identifying the key entities and how they relate to each other. An example of this for the above requirement would be:
Entities:
- Listing – with attributes like Location, Title, Description, Property Type (Residential or Commercial), Rental Type, Layout, Price, Availability, Preferred Tenant Type, Is Pet Allowed?, Is Smoking Allowed?
- Amenity – with attributes like Amenity’s Category, Name
Relationship between Entities
Listings contains Amenities
The Conceptual Model served two main purposes for me – it defined the scope of the application, and it clarified the implementation of written requirements. I tried to clearly identify many-to-many relationships, like Listing and Amenity, which would introduce a bridge table between them to convert many-to-many relationships to multiple one-to-many relationships. I visualized these using pen and paper before moving to a tool.
Transitioning into Logical Data Model:
Then, I translated my conceptual model into a Logical Data Model. A logical model is a conceptual model to which data normalization techniques have been applied and for which all of the attributes have been identified. For me, this meant adding more details to my entities, including attributes and their data types, relationships and cardinality, and normalization of tables.
I found it much easier to accomplish this using MySQL workbench’s EER Diagram creator. It also helps visualize the tables and their relationships well.
Initially, I used ENUMs for controlled fields (e.g., rental type), but soon converted these into lookup tables to keep the database flexible and scalable for the future. I focused on normalizing the tables in this stage, applying the Third Normal Form. This Logical Data Modeling step completed with the creation of a complete schema that included 37 tables, properly defined keys and constraints, and logical implementation of lookup, transaction, and system tables. Lastly, I indexed all the foreign keys for performance optimization.
The final ERD for the Logical Data model is displayed below.
Final Implementation in SQL Server
I implemented the final schema in MS SQL Server using DDL queries. While the initial modeling was easier with MySQL Workbench because of its UI-based modeling, my experience and comfort with MSSQL was the only reason I chose it.
You can find the following in my Github Repository:
- Full DDL Script
- Dummy Data Insertion Script
- ERD
- Data Dictionary
- Requirements
What I learned and Implemented
Starting with vague requirements, I created a detailed, actionable set of specifications using design thinking with minimal direction. I improved my research skills, doing it with the intention of implementation. I was able to apply Conceptual and Logical Data Modeling techniques to a real-life scenario and normalize a database.
I also got to implement these SQL concepts during the project:
-
CREATE TABLE,DROP TABLE IF EXISTS– to define and reset schema - Use of various data types and their actual impact on a real database
- Primary and Foreign Keys – to define relationships and integrity
- Other constraints like
NOT NULL,DEFAULT,UNIQUE - Implementing Relationships
- Lookup Tables – for flexible ENUM alternatives
- Bridge Tables – to resolve many-to-many relationships
-
IDENTITY– for auto-increment primary keys - Indexing – applied on foreign keys for performance optimization
If you're learning SQL or starting in Data Engineering, I encourage you to use my schema as a playground. It’s full of real-world entities and dense enough to practice joins, aggregations, constraints, and more.
I hope this post helps others see that getting started is more important than getting it perfect. If you've ever built something from vague specs, I’d love to hear how you approached it. Feel free to reach out or comment on LinkedIn!
Top comments (0)