DEV Community

Cover image for Mastering Relational Database Design: A Comprehensive Guide

Mastering Relational Database Design: A Comprehensive Guide

Louai Boumediene on May 06, 2024

Introduction In today's data-driven world, storing and managing information efficiently is crucial for businesses and organizations of a...
Collapse
 
danbailey profile image
Dan Bailey

Wow. This might be the best post I've seen on here re: RDBMS. Seriously. Very comprehensive, well-written, and succinct at the same time. Kudos!

Collapse
 
louaiboumediene profile image
Louai Boumediene

Thank you Dan, Really appreciate that

Collapse
 
dalun profile image
Dan Lunecki

This is well done - compact and comprehensive! Nice things to possibly add:

  • Table Data Population - initial
  • Table Data Population - incremental (maintaining/refreshing)
  • Views Thank you!
Collapse
 
louaiboumediene profile image
Louai Boumediene

Very nice suggestions, for sure I'll add them ASAP, Thank you very much Dan

Collapse
 
efpage profile image
Eckehard

Great post, thank you!

It's worth to mention that - depending on your data - other types of db could be used:

  • No-SQL (schema-free) databases could be beneficial, if structures are dynamically changing or if structures are still under development. As structures can be introduced with the data, they can handle even unknown structures as long as data are provided in an appropriate format
  • TSDB (time series databases) like influxDB store time series data very efficiently. And they provide methods to aggregate or interpolate large time series very fast. You can do the job using an RDBMS, but operations will probably be much less efficient.

RDBMS can be very efficient, but setting up a data model can be challenging - especially if you are new to this topic. So, it's always good to know the different options and to select right one.

Collapse
 
tracygjg profile image
Tracy Gilmore • Edited

I don't think that a no-sql or document database is absolutely schema-free. Having a schema helps maintain consistency in the data structure and quality (validation).

Collapse
 
efpage profile image
Eckehard

Surely not, and finally you need to know the structure to work with your data. But because the Schema is driven by the data, it can be easily expanded. This makes validation much harder and in the worst case you fill your database with lot´s of useless data. So, you will need to do some schema-exploration and garbage collection working with the database.

As a main difference, the schema in an RDB is maintained by the database, while in a no-SQL db it is maintained by the supplier and consumer of data. The database is relative agnostic about the data it stores, which is a real paradigm shift. But it depends much on your task, it can be an advantage or a disadvantage.

Collapse
 
houdabenkhelif profile image
houda

that's more than what I can understand in one lecture (or 10🙂) , that was pretty direct and clear thank u for this explanation (You could have explained this all at class ; We wouldn't have died of boredom from BDD's teacher explanaition )

Collapse
 
louaiboumediene profile image
Louai Boumediene

Thank you my biggest supporter ✨

Collapse
 
flosej profile image
flosej

Very clear and well explained 👏
In the 2NF, it was not the course_name column violating the rule ?

Collapse
 
louaiboumediene profile image
Louai Boumediene

Absolutely true, the coure_name is the subject matter not the grade,
Corrected

Thank you very much flosej

Collapse
 
chahid_kari_4494dd3097d6b profile image
Chahid Kari

Man i like your explaining method the information is stored directly in my database

Collapse
 
bandook profile image
Brian Clarke

Nicely done. A great refresher or solid beginning knowledge.

Collapse
 
seifdz09 profile image
Seif elddine

As always your articles are so pretty
-nice organization
-variety of informations
Congratulations 🎉🎉

Collapse
 
davidpro profile image
David

Just Wow!!! That truly very well structured and in depth guide, thanks!

Collapse
 
louaiboumediene profile image
Louai Boumediene

Thank you I really appreciate that

Collapse
 
tanzimibthesam profile image
Tanzim Ibthesam

Great article for a revision you nailed it. You can look forward to entity sub type and super type in future.

Collapse
 
rajrodrigo profile image
Nicholas Rodrigo

This is very precise, simple. Good for an expert and for a total beginner.

Collapse
 
fireandsafetyjobs profile image
Fire and Safety Jobs

Good post

Collapse
 
sasikumar profile image
Sasikumar Chandrasekar

Thank you for providing such valuable content.

Collapse
 
phamnghia21 profile image
Nghia Pham

Thank you for great post.
Can you please tell me when should I need to normalize in my DB.

Collapse
 
louaiboumediene profile image
Louai Boumediene

Well, applying normalization on ur database schema design is tropically done, among the latest steps you do in the process, where after u define the entities, primary keys, foreign keys, indexes and refine the structure, you start applying the normalization rules up from 1NF to 2NF to 3NF.

Hope that clarified things a little.

Collapse
 
rakibrahman profile image
RakibRahman

Great post!

Collapse
 
vinit1168 profile image
vineet

I liked how he/she explains everything in a simplified manner. Thanks !

Collapse
 
rcls profile image
OssiDev • Edited

Thank you for this exhaustive article! A truly wonderful read. I also learned something from this.

I never looked up the term "normalization". I've taken a bigger interest into looking up the definition of specific terms in our industry in recent year, as it is driven by them. To me the word "normalization" always sounded like a task, something that was done after you've built your database, to improve it's performance.

This article showed me that's actually not the case. It's actually a design principle.

It is a principle (or a set of principles) that I have (luckily) followed throughout my whole career. I've also gotten a bit better at it through practice over time. Since we're continuously applying those principles to our database design, we don't have complete the work later, when it's much harder.

So thank you for teaching me something!

Collapse
 
russellmoore1987 profile image
RussellMoore1987

Very well done! Very comprehensive!

Collapse
 
cenriqueortiz profile image
Carlos Enrique Ortiz

Thanks. Well written article. Very clear, concise, well explained.

Collapse
 
akramsakib profile image
Akram Sakib

This could be the best interview recap about RDBMS.

Collapse
 
tkouleris profile image
Thodoris Kouleris

That’s an excellent job! Very thourough and a great place to start someone the database journey. Keep up the good work!

Collapse
 
pentatonicfunk profile image
Hendrawan Kuncoro

noice!!

Collapse
 
artu_hnrq profile image
Arthur Henrique

Nice post!
I just reviewed several details that were already rusty

Collapse
 
artu_hnrq profile image
Arthur Henrique

I was just wonder:
What is the difference between many and zero-to-many + one-to-many in ER diagrams.
I felt as the first was contained in the both two

Collapse
 
iamak profile image
Ajithkumar P S

Terrific post! Thanks for sharing.

Collapse
 
aquilamuriuki profile image
AQUILA MURIUKI MUTUGI

nice that 👌👌👌

Collapse
 
ricardogesteves profile image
Ricardo Esteves

Nice articles, really good! Thanks for sharing it!

Collapse
 
ranjithkea profile image
Ranjith

Great one.

Collapse
 
abdullah-dev0 profile image
Abdullah

wow nice one

Collapse
 
hoviscorral profile image
HovisCorral

An indispensable resource for anyone seeking to build robust and efficient database systems. Reddy Anna Login

Collapse
 
ude_hamiltonikedi_f8785e profile image
Ude Hamilton Ikedi

Great content. Thank you

Collapse
 
maxwell_fernandes_0bb70f4 profile image
Maxwell Fernandes

Top!!!

Collapse
 
diegoar98 profile image
showbart98

Great post!