Open In App

Points to remember for Database design Interview

Improve
Improve
Like Article
Like
Save
Share
Report

In this article, we are going to discuss some frequently asked questions related to database design in an interview. Here, we will discuss some points to keep it in mind while facing a database design interview.  

Database Design is one of the complete domain in itself in the Information technologies industry. And every IT organization focus on the database design part while developing an application or project. In this domain, You can work specifically as a database designer and it’s demanding in the IT industry. 

For the Database designer role, you must have strong knowledge of the fundamental principle part of the database design. You must have strong knowledge of how to design a good database model for an application as per the specified requirements of clients.

The interviewer asked to design the database for his personal use. The best approach to proceed with this type of question is to proceed step-by-step and follow the top-down approach that moves from a broader view to the narrower one. One might also notice the similarities between this approach and the approach for object-oriented design.

1. Handle Ambiguity :

Database questions often have some ambiguity, intentionally or unintentionally. Before proceeding with design, one must understand exactly what you need to design.

The main problem asked was to design a system to represent an apartment rental agency. Now designers will need to know whether this agency has multiple locations or just one. And so the candidate should also discuss with your interviewer how general should be the design. 

For example, it would be extremely rare for a person to rent two apartments in the same building. But does that mean that the design shouldn’t be able to handle that(Maybe, maybe not). Some very rare conditions might be best handled through a workaround (like duplicating the person’s contact information in the database).

2. Define the Core Objects :

Next, the designer should look at the core objects of the system that is required to build on. Each of these core objects typically translates into a table. In this case, our core objects might be Property, Building, Apartment, Tenant, and Manager.

3. Analyze Relationships :

One main question asked was Outlining the core objects should give us a good sense of what the tables should be. How do these tables relate to each other and need to answer Are they many-to-many or One-to-many.

If Buildings has a one-to-many relationship with Apartments (one Building has many Apartments), then this might be represented as follows.

Apartment ID int
ApartmentAddress varchar(100)
BuildingID int
BuildingID int
BuildingName varchar(100)
BuildingAddress varchar(500)

Note : 

The Apartments table links back to Buildings with a BuildingID column.

If a company wants to allow for the possibility that one person rents more than one apartment, the designer might want to Implement a many-to-many relationship as follows.

TenantApartment's ""
TenantID I int
ApartmentID I int
ApartmentID int
ApartmentAddress varchar(500)
BuildingID int
TenantID int
TenantName varchar(100)
TenantAddress varchar(500)

The TenantApartments table stores a relationship between Tenants and Apartments.

4. Investigate Actions :

Finally, the design is ready to fill in the details into the database. Walkthrough the common actions that will be taken and understand how to store and retrieve the relevant data. Design final structure needs to handle lease terms, moving out, rent payments, etc. Each of these actions requires new tables and columns.


Last Updated : 14 Oct, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads