SQL Advanced: Advanced Database Design

Name: SQL Advanced: Advanced Database Design
Author: Eric Grose

Gain experience designing an advanced and optimized database

Preview the first lesson free — get full access to all 3 lessons.

Course: On-Demand

These lessons will be covering the concepts surrounding advanced database design. In this lesson will gain experience with scaling a database, utilizing partitioning and sharding. Partitioning allows for efficient management of large tables by dividing them into smaller, more manageable segments while charting distributed data across multiple databases or servers. Let's say we work for a manufacturing company and have a vendors table that contains a large number of rows. Let's go ahead and view this table by pressing the play button. Now, this isn't a large table and is for education purposes, but it does contain a vendor ID category ID of the product. The country where the vendor resides and the start date when they became our partner. Our first example we will review range partitioning. This is a technique that will split data based on a range of values, oftentimes a range of dates. So partitioning takes place during the create table process. So we'll go ahead and create a table named Vendors underscore range and include all the columns and their associated data types. And a key takeaway is that if you want a partition by a column, it must be one of the primary keys. So for this example, we'll have a vendor ID and a start date as our primary keys. Then we'll use a partition by keyword, followed by the type of partition we want, which in this case is range. Then last list the column we want to partition by, which is the start date column. Let's go ahead and create this table by pressing the play button. And if we click on our tables, then right click it refresh. We now see our vendors range table with the Capital P icon to indicate that this is a partition table. A different approach to a similar use case would be to introduce less partitioning that splits data based on predefined values. So this time we'll create a table named vendors underscore list. Still keep all the columns and their associated data types, but we want to create a predefined list based on the country of the vendor. So we'll go ahead and include country as one of our primary keys. Then we'll use a partition by keyword, followed by the type of partition which in this case is list and last the column we want to partition by, which is the country column. Let's go ahead and create this table by pressing the play button. Now that we've created our table, let's create our predefined list or the logic for our partitions. So again, we'll use a create table statement, give our partition table a name which is North America. And here's where this differs from a traditional create table statement. We use the partition of key words based on our main table, which is the vendors list. List partitioning is based on a predetermined list of values. So we'll use the four values in keywords to define the list of values we want. In this case, USA, Canada and Mexico. Let's go ahead and create this partition table by pressing play. And once again, we go to our tables. Right click it. Refresh. Expand out our vendors list table. Go to the partition section. Expand this out. We now see our vendors. North America. Partition table. Now we defined our logic, but we want to go ahead and insert some rows into this table. But we use an insert into statement on the partition table name with the column headers within this table. And now, rather than using the values keyword to insert row by row, we can write a query to go ahead and populate this table with the values we want. But we'll use a select to bring in all columns from our original vendor's table that we viewed at the top of this lesson. We use a WHERE clause to do a conditional statement on the country column. The in keyword to define a list. And now we'll match our list values for USA, Canada and Mexico here. Let's go ahead and insert these rows by pressing the play button. And now if we click on our partition table, right click, go to The View and edit data. Select all rows. You can see the rows that we expected are in this partition table. Now, in a real world scenario, we would go ahead and repeat this process for our European vendors or Asian vendors and so on and so forth. Next, let's say our company is growing and we have even more rows than our original vendor's table. We could also choose to chart our data by using a technique called horizontal charting. This will distribute rows across multiple servers, or in this case, databases. So if our data volume becomes too big, we could create a database named Chart one. Let's go ahead and press the play button as well as chart two to distribute the massive amount of rows. Let's go ahead and press the play button. If we go to the top, click on our databases, right, click it, refresh. Now, if we scroll down to the bottom, we can now see our newly formed databases where we could define our logic how to distribute these rows. Thanks for watching. Stay tuned for the next lesson, where we work with slowly changing dimensions within a database.

Advanced Provider : Eric Grose 3 Lessons · 16m in Arabic, English

Course Description

In these lessons, you will learn techniques for partitioning and sharding, which are essential strategies for scaling databases. This will help you distribute data across multiple databases or servers while maintaining seamless data access and management. You will also begin to understand the significance of slowly changing dimensions (SCDs) in preserving historical data accuracy. There are various techniques to effectively track changes over time and maintain a historical record for timeline analysis. You will also learn about the rare, but highly strategic, application of denormalization to enhance query performance. Gaining expertise in these tasks will help us identify the specific scenarios where denormalization can actually streamline data retrieval and optimize overall database performance.

In these lessons, you will begin to understand the intricacies of partitioning and sharding for scalable data management. Learn to use each type of slowly changing dimensions (SCDs) to preserve data accurately. We will also determine the specific use cases where denormalization can actually enhance query performance and streamline data retrieval. These skills will empower you to implement strategies for handling large datasets.

What You'll Learn

Apply partitioning and sharding techniques to scale databases and distribute data across multiple databases or servers while maintaining seamless data access and management
Use each type of slowly changing dimensions (SCDs) to preserve historical data accuracy and track changes over time for timeline analysis
Identify the specific use cases where denormalization can enhance query performance and streamline data retrieval
Classify data across multiple databases for scalable data management
Explain how historical data changes over time and maintain a historical record
Implement strategies for handling large datasets

Key Takeaways

Partitioning and sharding are essential strategies for scaling databases by distributing data across multiple databases or servers.
Slowly changing dimensions (SCDs) preserve historical data accuracy and provide techniques to track changes over time for timeline analysis.
Denormalization is a rare but highly strategic application that can streamline data retrieval and optimize overall database performance in specific scenarios.
Mastering these techniques empowers learners to implement strategies for handling large datasets.

Frequently Asked Questions

What topics does this course cover?

The course covers partitioning and sharding for scalable data management, slowly changing dimensions (SCDs) for preserving historical data accuracy, and denormalization use cases for enhancing query performance.

What skills will I gain from this course?

You will gain skills in chunking, data partitioning, and denormalization.

What will I learn about slowly changing dimensions?

You will learn the significance of slowly changing dimensions (SCDs) in preserving historical data accuracy, along with various techniques to track changes over time and maintain a historical record for timeline analysis, and how to use each type of SCD.

When is denormalization useful according to this course?

The course explains that denormalization is a rare but highly strategic application, and helps you identify the specific scenarios and use cases where denormalization can streamline data retrieval and optimize overall database performance.

Who is this course for?

This is an advanced SQL course on advanced database design, focused on techniques for scaling databases and handling large datasets through partitioning, sharding, SCDs, and denormalization.