Notes on refreshing and improving my knowledge on SQL Database Design

Last updated on 15th Oct. 2024
In my past software industry career (1984 to 2002), I had done some amount of SQL database related work but not much. So I was not a database design expert. But I could understand simple to medium complexity database designs and use SQL on command line or through programs to do CRUD operations on the database.

Given the above background and given that some web dev projects use SQL databases instead of NoSQL databases, I would like to refresh and improve my knowledge on SQL database design. This post has notes related to this activity.
==================

Around 13th to 14th Oct.:
About yellow and red key icons, and blue, red and white diamond icons before columns in entities in ER diagrams: What do the mysql workbench column icons mean, https://stackoverflow.com/questions/10778561/what-do-the-mysql-workbench-column-icons-mean explains what these icons stand for.

Excellent article on ERD: What is Entity Relationship Diagram (ERD)?, https://www.visual-paradigm.com/guide/data-modeling/what-is-entity-relationship-diagram/ . It seems to be consistent in showing strong relationship (solid line) only when child entity PK has part of parent entity PK. If child entity only has a FK which maps to PK of parent, the relationship is shown as weak (dashed line). See its "Movie Rental System" example where all relationships are shown as weak (dashed lines).

The components and features of an ER diagram, https://www.lucidchart.com/pages/er-diagrams#section_3

==========
Am quickly browsing through (what seems to be) an 11 part course on MySQL by "Eduonix Learning Solutions" which is 7 years old: (1st video) Learn Database Design with MySQL | Introduction to Database and MySQL | Part 1, https://www.youtube.com/watch?v=Dlx8_LJ3rek , Aug. 2017. It is slow paced and so I have skipped lot of parts but it seems to be quite thorough.

Good and clear article but stops after initial stage of design (rest is covered in book that has to be bought):

Database Design Succinctly by Joseph D. Booth, https://www.syncfusion.com/succinctly-free-ebooks/database-design-succinctly/introduction ... Downloaded pdf file: database-design-succinctly.pdf, 87 pages. - This seems to be a great book to quickly come to speed with DB design. It covers Conceptual Model, Logical Model and Physical Data Model. It also covers DB normalization. The examples given are clear. It refers to SQL Server when specific database product aspects come into play like date and time datatypes. 

Free ebook having organized articles from Stack Overflow Documentation: https://books.goalkicker.com/SQLBook/ ... pdf file: SQLNotesForProfessionals.pdf, 166 pages, more into SQL rather than db design.

Free ebook on MySQL having organized articles from Stack Overflow Documentation: https://goalkicker.com/MySQLBook ... pdf file: MySQLNotesForProfessionals.pdf, 199 pages.

===========================
14th and 15th Oct. 2024:

MySQL Workbench docs: Chapter 9 Database Design and Modeling, https://dev.mysql.com/doc/workbench/en/wb-data-modeling.html

Gives some info. on coloured icons in MySQL EER (Enhanced Entity Relationship) diagrams: SQL-01d - Understanding EER Diagrams, https://www.youtube.com/watch?v=jzAC6cKCuSI, 14 min. 29 secs, Jan. 2016.

=========

Design Principles & Normalization Tutorial | Learn Database Design with MySQL | Part 6, https://youtu.be/vIsO34Ei1-4?t=669 : Starts database design for a simple products, customers and reviews website. Saw most/all of it.

Mapping Out Our Schema | Learn Database Design with MySQL | Part 7, https://www.youtube.com/watch?v=R4ZJ4ZLJoKs , around 15 mins. Saw most/all of it.

How to Create Tables & Columns in MySQL | Importing & Exporting in MySQL | Part 8, https://www.youtube.com/watch?v=DC12vEjiILY , around 18 mins. Saw most/all of it.

Learn Relations & Foreign Keys in MySQL | Junction Tables in MySQL | Part 9, https://www.youtube.com/watch?v=wXRGQapf_rg , around 19 mins. Saw most/all of it.

Learn Command Line & Inserting Data in SQL | Basic Select Queries tutorial| Part 10, https://www.youtube.com/watch?v=wps9MZPXx2g , around 44 mins. I browsed through it.

Learn How To Update & Delete Data in MySQL | Aliases & Joins in SQL | Part 11, https://www.youtube.com/watch?v=QHP_ydF1xAU, around 28 mins. Saw fair amount of it, especially the second half part.

Learn Union, Concat & Count Functions in SQL | What is IN Clause | Part 12, https://www.youtube.com/watch?v=7u9MSu4CRWY , 21 mins. Saw most of it.

Full 12 video playlist: https://www.youtube.com/playlist?list=PLDmvslp_VR0yhBmSTEbDGcoeZqOZMJQ_L [At end of 12th video the speaker talks of what is covered in next video. So there seems to be further videos in this series but that's not part of the playlist and perhaps is not available on youtube.]

----
Useful video about ER diagram creation facility from existing database in MySQL Workbench: Create ER Diagram of a Database in MySQL Workbench, https://www.youtube.com/watch?v=BjPsm7Ny1MA , 7 min 6 secs., May 2020. 
The video mentions https://www.mysqltutorial.org/getting-started-with-mysql/mysql-sample-database/ as the source of its sample database. The page has an ER diagram too but it does not use coloured icons and the notation seems to be slightly different - perhaps MySQL Workbench was not used to create the ER diagram. The video shows ER diagram (created by MySQL Workbench) with coloured icons and what seems to be current notation.

Comments

Post a Comment