Advanced Database Design For a Relational DB w/ MySQL
4.2 (209 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
11,122 students enrolled

Advanced Database Design For a Relational DB w/ MySQL

No basics here. Advanced learnings and best practices for those who want to get to their next level.
4.2 (209 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
11,122 students enrolled
Created by Michal Juhas
Last updated 2/2018
English
English [Auto-generated]
Current price: $16.99 Original price: $24.99 Discount: 32% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
  • 2 hours on-demand video
  • 10 articles
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • To Learn Database design Process
  • Learn what are the steps to create a sound database design
  • You will be able to design a relational database from scratch
Course content
Expand all 26 lectures 01:55:52
+ Laying Down The Foundation
2 lectures 10:55

There are 9 essentials:

  1. Understand the database’s purpose (OLTP/OLAP?)
  2. Get the right tool (MySQL Workbench)
  3. Gather the requirements for the database (biz, tech, ops)
  4. Standardize the naming convention
  5. Enforce relationships (data integrity)
  6. Use the appropriate data types (use a variety of data types)
  7. Be conscientious when modeling the data (do what’s right!)
  8. Include indexes when modeling
  9. Document your work & keep track of changes (GitHub repo)
Preview 05:32
  1. Poor design/planning
  2. Ignoring normalization
  3. Poor naming standards
  4. Lack of documentation
  5. One table to hold all domain values
  6. Using identity columns as your ONLY key
  7. Not using SQL facilities to protect data integrity
  8. Trying to build generic objects (common reference table)
  9. Lack of testing
  10. Poor deployment workflow (lack of environments)
Common Database Design Mistakes
05:23
+ Database Purpose
3 lectures 10:56
Be Clear About The Database Purpose
03:42

When you start your database design the first thing to analyze is the nature of the application you are designing for, is it Transactional or Analytical. You will find many developers by default applying normalization rules without thinking about the nature of the application and then later getting into performance and customization issues. As said, there are two kinds of applications: transaction based and analytical based, let’s understand what these types are.

Transactional: In this kind of application, your end user is more interested in CRUD, i.e., creating, reading, updating, and deleting records. The official name for such a kind of database is OLTP.

Analytical: In these kinds of applications your end user is more interested in analysis, reporting, forecasting, etc. These kinds of databases have a less number of inserts and updates. The main intention here is to fetch and analyze data as fast as possible. The official name for such a kind of database is OLAP.

If you think inserts, updates, and deletes are more prominent then go for a normalized table design, else create a flat denormalized database structure.

OLTP & OLAP
06:53
Separate OLTP and OLAP Database Servers
00:21
+ Best Practices
12 lectures 01:11:24

There is no reason to be inconsistent in your naming except sheer laziness.

Naming conventions might not appear important during the design. In reality, names provide the insight to understanding a model. They are an introduction and should be logical.

Inconsistent naming serves no purpose. It only frustrates developers who must access the data, administrators of the database, and modelers who must make changes in the future.

Weak naming conventions also lead to errors in development because the naming is not consistent.

Hand-in-hand with documentation, using a naming convention makes it in the future for someone to understand the model.

Preview 14:41
Use Reference Tables
10:53
Generate DDL Scripts from MySQL Workbench
06:55

Generate the DDL scripts, store these in version control (e.g. GitHub). 

Other scripts that are also worth to be stored in version control: scripts to populate initial data (e.g. registry entries, lookup tables: country codes etc), export/import, migration, conversion between different applications & versions. 

If you have multiple scripting files, make a master script that will call those scripts with the right order dependencies. This self-maintenance approach cost efforts and error prone. Often the only reliable way to migrate is by drop the database and create the new one using the scripts from version control, this process can cost downtime.

Use a database tool (e.g. MySQL Workbench) that tracks the changes and generates migration scripts from diff operation

Save Scripts in GitHub's Repository
10:46
Deploy Your Scripts to Staging, then to Production Environment
00:27

Indexes are important when considering queries on the data. When modeling, you should consider how the data will be queried. Take care not to over-index. Indexing revolves around query optimization.

Preview 09:13
Which Columns to Index?
00:32
When to Use a View?
00:17

Each table must have a primary key.

Have a Primary Key in Each Table
07:18

Natural key vs artificial key. 

Use defined sequence object if you need to generate sequence (e.g. to generate primary key during insert)

When deciding which field or fields to use as keys in a table, always consider the fields that users will be editing. It’s usually a bad idea to choose a user-editable field as a key.

Use Artificial Keys
09:12
Use Integrity Checks As Much As You Can
00:44

Lack of database documentation is evil. Document your database design with ER schemas and instructions. Also write comment lines for your triggers, stored procedures and other scripts.

Provide documentation, describe all tables & relationships, DDL. The application programmers should be able to find documentations about any triggers, constraints & store procedures in your database.

Documentation is often an afterthought. When schedules are short, documentation gets ignored. Yet, this is technical debt with a high cost. Cutting corners during the development cycle will accrue costs in the future for database changes, problem identification, tracking bugs and for understanding the data model and nature of the data.

Document Even If You Have No Time For It!
00:25
+ Watch Out
5 lectures 10:25
Avoid a Common Reference Table
08:42

Learn why to avoid triggers.

Avoid Triggers
00:34
Anyway, What the Triggers Could be Good For?
00:21
Don't Have All Columns NULLable
00:23
Make Sure You Understand Database Normalization
00:23
+ See You Next Week
1 lecture 00:59
Would You Like To Hear More?
00:59
Requirements
  • Basic MySQL knowledge
  • This course is not for SQL beginners. It requires you to know SQL - we do not go in depths of the queries
Description

Learn the basics elsewhere and get the advanced stuff here:

  • OLTP vs. OLAP database design
  • Reference tables
  • Database deployment
  • Naming conventions best practices
  • And a lot more for those who want to get to the next level

If you want to improve your software development career, get into database admin or database design, or are just curious on this topic, this course is for you.

Who this course is for:
  • Web Developers
  • Database Designers
  • Full-stack Software Engineers