How SQL Databases Can Reduce Errors in your Web App Code

Often times web developers will not fully exploit the powerful functions you have built into an RDMS. SQL based databases have many tools that protect your database from being corrupted.

In this video, I talk about how my database rules prevented a simple data error from creeping into my web application.

My popular courses:

My business courses:

My social links:

Thanks!

Stef

[taariqq]

IMHO, you should not avoid the 'jargon'. Someone who does not understand a few terms will look it up, but more importantly there are people who are established in the industry and watch your videos.

Very important advice, and pointers, for the beginners.

[gadget]

very good observations; particularly the concepts of stored procedures and proper indexing. Triggers are also incredibly useful

[jeremyanderson]

I love this video format, alot more nerdy than your normal content.

[devonkiss]

This video needs waay more views in my opinion. Always love your videos and work Stefan! Many people this day and age have no clue what a real database `routine` is.

[macolulu]

Real life story about the importance of database knowledge. No wonder every time I went to interview as a Java developer, they asked many sql questions. And I found utilizing stored procedures could solve lots of business logic that complicated for Java code. I will definitely enhance that. Great great experience 👍👍

[ImperialArmour]

Data scientist here working with petabytes of data.

Totally agree, indexing stores the LOCATION of the data your queries retrieves - the correct way is to index on the JOIN and WHERE columns. Your developer might have applied to fields such as those storing large range of integers and decimals(non-categorical values, including names & addresses), or worse - applying it on every column which will take up more disk space than the physical table itself. With indexes, less is indeed more.

Most data science folks - especially the younglings out from school, also abhor the RDBMS for some reason and just use really silly java based storage applications like Hadoop which is incredibly slow and prone to job failures . to make it faster is to brute force way add hardware to it(expensive!)


I've been using relational databases for many years in my career and the RDBMS still surprises me on optimisation tricks it has up its sleeves!

[erikslorenz]

This may be the best vlog you've ever done.

Oh my God once I realized what postgres could do it abstracted so much crap from my server.

[dcocrypto]

I was able to radically speed up my Wordpress site by optimising the MySQL database using the following steps.

1. Make a backup of your database or export in phpmyadmin.

2. Run a scan with “plugin garbage collector” and delete MySQL tables from unused or deleted plugins.

3. Run “wp-optimise” plugin to clean up database.

For possibly extra good measure

4. In phpmyadmin select all tables and select repair from drop down menu

5. Repeat step 4 but select optimise tables instead of repair.

6. Install “query monitor” plugin and check for slow processes and then find faster ways to achieve desired functionality.

Time elapsed 30 min. Combine with front end optimisation and quality theme and hosting = website load time under 1 second until Adsense code added :-)

[THEENIGMACODENAMED]

This topic should have attracted much more views. It's a very important one.

Very interesting indeed.

Regarding indexes, check your execution plan of all the queries; You will find how to setup the indexes if you can't figure it out from the data model.

[nickchervov]

Can’t say it more right, Stefan, good job 👍

[oladipotimothy]

Great Stuff, I guess the first Dev didn't know the volume of data will get pretty big later on... But I believe it all boils down to the fundamentals of CS in terms of search(Indexing is pretty important)

[busyrand]

I appreciate the lesson. I'd love a live walkthrough of the example and other examples of Production level code. Thank you.

[hanzlaahabib]

You are Awesome Stefan Thanks for the Overview

[ristekostadinov]

Core thechnique for optimising your app and make it scalable is to design your DB correctly and optimising some querry that do a lot of computation.

[tr]

In Java we have things like Hibernate which is object relational mapper, this framework usually able to create proper SQLs and make some stuff with db easy, but sure if you have some use cases which cant be covered by this framework well abstract those use cases in Java and implement appropriately. In large Enterprises switching from one db to another db sometimes on 5 year circle.

[Aggnog]

Hey Stef, I don't know to what extent your courses go, but I'd like to ask about what your take is on the current self-teaching ecosystem that's getting bigger and bigger in the last few years. As someone who started about a year ago I've noticed that there is a huge gap after the beginner stage and a lot of people get stuck not knowing where to go.

Almost all tutorials overlap each other and end up being aimed at beginners to grab a bigger audience. Project based courses on the other hand don't do much to teach you self sufficiency and don't carry over for projects that weren't included.


I've noticed that one of the main problems is that you don't know what you don't know and to that a good response I've seen is to go through the language documentation thoroughly and look at a lot of code which seems to be the way to go.

I apologize for my thoughts being a bit scattered but I'd like to know what you think the best path is to climbing out of the junior programmer stage.

Take care

[chiquitocastaneda]

Hi Stefan, I found it interesting but I didn't understand what you said, LoL... I hope when I am finished with your course I will be able to understand, but thank you Chiquito...

[mylegs]

Stefan, I have an assignment for one of my college courses to ask a few questions to someone who works in a career field that I am interested. I am studying computer science and if there was anyway I could ask you 3 or 4 questions that would be awesome. Thanks!

[devonkiss]

With regards to learning more about SQL databases and their native features... Is there a book or course that you can recommend Stefan?