How to design a database system

In your technical interviews, you might be asked to design your own database. In this shot, we’ll walk through on possible approach to this.

Step 1: Handle ambiguity

Database questions often have some uncertainty, intentionally, or unintentionally. Before you proceed with your designs, you must understand exactly what you need to design.

Imagine you are asked to design a system to represent an apartment rental agency. In this case, you will need to know whether or not this agency has various locations. It would be best if you also discussed with your interviewer how general your design should be. For instance, it would be extremely rare for a person to rent two apartments in the same building, but does that mean you could not handle it? Maybe, maybe not. In fact, some of these very rare conditions should be handled through a workaroundlike copying the person’s contact information in the database.

Step 2: Define the core objects

Next, we should view the main objects of our system. Each of these main objects typically converts into a table. In this case, our main objects might be Property, Building Apartment, Tenant, and Manager.

Step 3: Analyze relationships

Outlining the main objects should give us an idea of what the tables should be:

• How do these tables relate to each other?
• Are the relationships many-to-many? or one-to-many?

If Buildings has a one-to-many relationship with Apartments (one Building has many Apartments), then we would display this as follows:

Note: that the Apartments table links back to Buildings with a BuildingID column.

If we want to allow for the possibility that one person will rent more than one apartment, we might want to implement a many-to-many relationship:

The TenantApartments table stores a relationship between Tenants and Apartments.

Step 4: Investigate actions

Finally, we’ll fill in the details, walkthrough the general actions that will be taken, and understand how to store and retrieve the appropriate data. You’ll need to handle lease terms, moving out, rent payments, etc., each of which will require new tables and columns.

Large database design

While designing a large scalable database, joins (which are required in the above examples) are generally very steady. Thus, you must denormalize your data. Think carefully about how data should be used as you’ll probably need to copy the data in multiple tables.