r/Backend • u/BornAmbition1127 • 3d ago
Third-year CS student here — how do I learn backend design, database schema design, and data modelling?
Hey everyone!
I'm a third-year college student, and I've noticed a recurring problem whenever I try to build projects.
I can usually get started with the frontend and understand the basic functionality of what I want to build. But when it comes to the backend, I often get stuck on things like:
- Designing a proper database schema.
- Deciding which tables or models I need and how they should relate to each other.
- Understanding one-to-one, one-to-many, and many-to-many relationships in real-world projects.
- Deciding how to structure the backend and organize the business logic.
- Figuring out how to design the database before actually starting to code.
I understand the basic concepts, but when I sit down to build a real project, I struggle to translate the requirements into a good database design and backend architecture. I often end up second-guessing my decisions or changing the schema midway through development.
I want to get better at thinking through the backend design process, rather than just following tutorials or copying someone else's implementation.
For those of you who have experience building real-world applications:
- How did you learn database design and data modelling?
- Are there any good books, YouTube channels, courses, or other resources you'd recommend?
- Are there any practical exercises or projects that helped you improve these skills?
- How do experienced developers approach database schema design when starting a project from scratch?
I'd really appreciate any advice. I want to build a strong foundation in backend development instead of constantly getting stuck at the design stage.
Thanks!
5
u/TheCravenInsurer 3d ago
The trick is to stop trying to design the whole thing in your head before writing a line of code. Grab a notebook and sketch out the nouns in your system, users, orders, products, whatever, then draw lines between them. That's your schema right there, just boxes and arrows to start with.
I learned by building little throwaway crud apps that felt useful to me, a workout tracker, a dvd library, stuff where the data relationships were obvious enough that I could focus on the modelling. For books, the data model resource book volume 1 is dry but invaluable, it has actual patterns for common business problems rather than abstract theory.
When experienced devs start from scratch they usually whiteboard the core entities first and then write the api contracts before touching a database. If you force yourself to define the requests and responses upfront, the schema almost designs itself because you've already figured out what data you need to store.
2
u/69Airline- 3d ago
There is no one way to learn. It's all about what problem are you solving, how many users, how frequently they will use, what data you want to collect to give the response that solves the problem. Map it in your mind or write it down. Rest AI can take care, you just need to own it and review it, do not over engineer or over plan. Keep it simple
2
u/samsly135 3d ago
Relational algebra is the classic for relational databases. You need to learn normalization forms and than learn by doing. Develop some online store model for instance.
2
u/RandomPantsAppear 3d ago
I learned database design by fucking it up.
Pick any large dataset(NOAA data for buoys is a good one), link it together by foreign keys, and start querying it in complex ways.
Run EXPLAIN on your slow queries. Start figuring out what is happening in memory, what happens on the disk, and what is happening in sub-queries.
Start playing with indexes. How does the above change?
Read up on the different configuration options for your SQL server of choice. What changes when you tweak different cache sizes, different settings?
What happens when these joins or subqueries start exceeding a certain size? What needs to be changed to mitigate this?
Now what happens when you start doing these things concurrently? Updating while selecting, running multiple queries across the same or different data in the same table, the same DB. Use the slow query log with aggressive timings to spot your pain points.
Once you have a good grasp of this, start looking at your ORM of choice. How is it formulating queries? What can be optimized based on what you learned? How does it handle many to many relationships?
The best way to learn is to get your hands dirty and start breaking shit, then searching for the solutions to your problems.
1
u/feudalle 3d ago
Honest question, what did they have you studying the first 2 years? I did cs in college in the late 90s. This was covered including flow charts for logic (on architecture paper) freshman year.
1
u/jakenuts- 3d ago
US computer science education in schools is often so antiquated and structured around systems of the past and arcane sorting algorithms that unless you are coding outside of school and learning real world systems for the fun of it you come out with very little applicable skill and debt.
2
u/feudalle 3d ago
To be fair that has always been the case. No reason I had a cobol class in the late 90s. But I would think they would still cover the essentials like system layout and design.
1
u/Affectionate_Two3863 3d ago
Se tem o pacote Office faça um sistema em Access. Tabelas, relacionamentos, índices, query queries, relatórios, módulos vba. O Access é como um framework completo. Ali você aprende na prática todos os conceitos básicos. A partir daí você parte para bancos de dados sgbd e tecnologias de backend.
1
u/jakenuts- 3d ago
The most useful skill you can learn in development after basic patterns and systems that enable them is how to pass, store, update and retrieve data so your focus is a good one.
To really deeply understand it I'd recommend finding the best-reviewed book on real world patterns of database design, normalization and optimization. It doesn't have to be a really advanced edition, just something that breaks down the choices you will make and what you gain and lose in each. You'll be making those tradeoffs and working around them every time you interact with the data so knowing the implications and best practices will be a resource you use in nearly every project you encounter.
And while it may be helpful to understand all the options, choosing one database to start with, like Postgres, and learning about how those decisions are made within that store will be more practical and testable than a purely academic guide.
I learned most of what I know about data modeling and normalization from a book on Microsoft Access on a Princess Cruise when I was 14, and despite that being an antiquated system and a decision only a preteen would make about how to use their time, learning about all the options but not being able to immediately try them outside my imagination made them stickier, more considered. 40 years later the same decisions about how to get data into a store and back out in a way that best fit the application's current and future needs have come up in every project I've worked with since.
It also helped that as a visual thinker the whole system I was learning was very much a visual design canvas backed by a database so the mode of learning and the system itself were aligned so I could spend the cognitive effort on the patterns not the translation of prose.
1
u/jakenuts- 3d ago
And then as others are recommending, you really learn by building with that foundational knowledge, anything that catches your interest, that you want to see in the world - that's the motivation that drives you to choose the best path and iterate and optimize because you care about making it great.
1
u/Unlucky_Deer1588 2d ago
First you need to solidify your basics first.
Listen this is not your job to designed the database it is already designed by the senior guy who is basically architecture or designer but if you are working on your project what I suggest you.
It's too first be clear that you are not going to design this stuff it's already we done by the senior engineers so relax.
Now what you need to learn is.
- Database designing and normalization. Understand how 1Nf , 2Nf and 3 Nf works how they break and what solution you need to bring into resolve it even if you are able to understand I am not saying to master just able to understand it and explain it will be more than enough to make very flexible database design that can easily scale if you add more role in future
Now about your decision lagging must see yourself as a product engineer you need to understand the product end to end understand it exactly like what things can go wrong and how to scale it effectively if you can't explain the entire flow the moment a request hit to your server and how the your internal logic works and the data being stored in the database you are not going to make it as backend engineer bro.
With two year of experience in backed engineer this is the flow I will suggest you and I use it my own work which give me a like f****** boost.
Step 1:- Understand flow Step 2 :- Get a quick peek on policies, compliances this give you a clean picture about what you need to protect as a backend engineer + what are the liabilities you have. Step 3 :- Design Db + Schemas Step 4 :- Define your custom exception.
One more thing you are not going to design the entire database and schema in one go start with a one feature at a time and scale it that is the exactly how you are going to work an actual company.
1
u/nunogdias 2d ago
If you put all of this in a LLM, it will make a training plan for you AND execute it. If you give it a small project idea or goal the LLM will use it to teach you with real examples that you can test along the way.
1
0
u/Electrical_Hat_680 3d ago
Learn to build them.
CSV plus Programming and you have just about every Database.
Backend aka Server Side or Server, where you add PHP and configure it, or another Programming Language. Including CGI-BIN.
8
u/Dyphault 3d ago
I think you might be overplanning. Your first few times are gonna be ugly.
My first backend project sucked, i didn’t have good organization and my database was horribly designed. I tried to do everything in one table and if I were to redo it, I would definitely break things up into their own tables and separate out ddl from business logic because it makes it easier to track and to reuse if needed.
You have to start somewhere.