DEV Community

Madhusudhan Reddy
Madhusudhan Reddy

Posted on

How to master database design?

This is my first post in dev.to (probably first in any of developer communities) and i am feeling nervous. I am not a good writer and my English is also bad. So please bear with me and my English.

I have been working with React and JavaScript for the past 2 years and felt this is the time to switch to backend. So i started giving backend interviews, as i am familiar with java, python, SQL and mongodb. In one interview i was asked to design Whatsapp (DB Models and Rest Apis). I started writing models(classes) and came up with 4-5 models (which is very less in my opinion). And i also confused as to a method should be in which model. Then i decided i want to learn Database design, and the best way to do (atleast for me) is to design and implement an application from scratch. I googled and found some app ideas, but i just wanted check here first. I am in the right direction? Is there anything i am missing? And also any app ideas to learn design are welcome.

Oldest comments (2)

Collapse
 
yaser profile image
Yaser Al-Najjar • Edited

I searched and read through tons of resources and heck when I was learning db design (and I still), I found that most of them are just useless.

The main reasons for the sissy resources existence are:

  • They're just theoretical, ex: books that I've read never touch a real world app...

After reading the book, you will end up saying:

the material in the book seems nice, but how do I design the db for my app? πŸ˜†

  • Most db design authors have never done designing for a "life-long" app, so if you believe what they say you will have a fragile design.

After years of trial and error in backend field, I can say that db design is something you learn over time.

But, there are many stuff that I can say that are the "super fundamentals" (in no order... If you have any others, plz lemme know πŸ˜‰):

  • Know the three basic relationships by heart: OneToOne, OneToMany, and ManyToMany.

That being said if I told you we need to design an app that keeps the names of the books which the users read (given that you have a Book and User table), you should know which relationship it is directly.

  • Never jump to NoSQL without playing with relational database for long so that you have a solid understanding of relational first.

  • Make the db works for you... Not the opposite.

Before you do any design, start with collecting the info of what your app is capable to do (business capabilities).

This will help you to make the db works for you cuz you made the design based on what you need.

I've seen many many people doing super complex queries, cuz they designed the db just to hold the info without having in mind how they will use the info and compose it into the UI.

  • Start small.

Never design to solve future problems. Your design should focus on solving the current problems with taking consideration into the future problems (so that you will adapt with no pain).

  • Have a UI first (opinionated one).

Never do the db design before having a wireframe (or preferably a mockup) for your app...

I believe best db/architecture designs are just result of a great UI.

Besides that, you will know what API you need by merely looking at the UI.

  • Focus on practice... Not theory.

Don't spend too much time reading theory, doing actual db design 10 times is better than reading 10 books about design.

  • Master the mighty skill in db design: Normalization.

  • Think OOP (opinionated one).

The more you think about classes and objects, the better.

This is cuz nowadays, every famous framework has an ORM that comes with it.

So, instead of thinking about diagrams and such, think (more) about the relationships among the objects.

  • Learn why do we need NoSQL in the first place?

That covers the ACID vs BASE.

And, it also covers the 4 main types of NoSQL: key-value, document, column-based, graph.

Understand the use cases of each, and ask yourself questions like: why most systems are currently using relational and not NoSQL?

  • Know the power of transactions.
Collapse
 
madhu0 profile image
Madhusudhan Reddy

Thank you @yaser Al-Najjar for your answer. This answer definitely have given me an idea of what to do next.

I've heard the term normalization many times in my short professional career, and i thought it was some kind of way to organize the data, but never bothered myself to dig deeper. But i think i made a mistake there.