DEV Community: Kate Naylor

Tips and tricks for SQL Server database (table) design?

Kate Naylor — Fri, 15 May 2020 15:38:16 +0000

Here are eight key database design rules, which are aimed to make your life easier. As it turns out, we often forget about painfully obvious things, so being prudent and wise stands above all rules.

#1: Figure out the nature of the application: OLTP or OLAP

When you start your database design the first thing to analyze is the nature of the application you are designing for, is it Transactional (OLTP) or Analytical (OLAP). If you think CRUD i.e., creating, reading, updating, and deleting records are more prominent then go for a normalized table design, else create a flat denormalized database structure.

#2: Break your data in to logical pieces

You probably will need to apply this rule if your queries are using too many string parsing functions like substring, charindex, etc. Sometimes it is better to break fields into further logical pieces so that clean and optimal queries could be written.

#3: Do not get overdosed with breaking your data

When you think about decomposing, give a pause and ask yourself, is it needed? For example, here’s the base of phone numbers. It’s rare that you will operate on ISD codes separately until your application demands it. So it would be a wise decision to just leave it as it can lead to more complications.

#4: Avoid non-uniform data

Focus and refactor duplicate data. For instance, in the below diagram, you can see “5th Class” and “Fifth class” means the same. If you ever want to derive a report, they would show them as different entities, which is very confusing.

#5: Avoid repeating groups

One of the examples of repeating groups is explained in the below diagram.These kinds of columns which have data stuffed with separators need special attention and a better approach would be to move those fields to a different table and link them with keys for better management.

#6: Choose derived columns preciously

If you are working on OLTP applications, getting rid of derived columns would be a good thought, unless there is some pressing reason for performance. In the case of OLAP where we do a lot of summations, calculations, these kinds of fields are necessary to gain performance.

#7: Centralize name value table design

Name and value tables means it has key and some data associated with the key. For instance in the below figure you can see we have a currency table and a country table. If you watch the data closely they actually only have a key and value. For such kinds of tables, creating a central table and differentiating the data by using a type field makes more sense.

#8: Do not be hard on avoiding redundancy, if performance is the key

Do not make it a strict rule that you will always avoid redundancy. If there is a pressing need for performance think about de-normalization. In normalization, you need to make joins with many tables and in denormalization, the joins reduce and thus increase performance.

As a bonus, I would recommend using dbForge Studio. The solution will help you to avoid a whole bunch of mistakes while developing and managing databases. The following features will be especially useful:

1) Database Diagram which shows the structure of an already created database. It often helps to decide whether tables and the relationships between them have to be redesigned;

2) Visual Table Editor provides convenient editing, filtering, sorting, copying of randomly selected cells, quick data export to INSERT query and many more;

3) Data Editor to view data, edit, and roll back changes, if necessary;

4) Source Control to make controlled changes to the database during teamwork with the ability to view the history of changes (including revision ID, date, author and comments);

5) Schema Compare to compare the developed and live versions of the database and understand what differences they have;

6) Query Profiler allows comparing the performance of modified queries with current ones.

Quick Check of SQL Table Contents With Data Visualizers

Kate Naylor — Thu, 07 May 2020 15:30:28 +0000

Visualize your SQL data to facilitate understanding and decision making with the Data Visualizers feature of SQL Complete. On many occasions, developers and DBAs need to quickly check the table contents and getting data in a readable format without much effort becomes a matter of importance.

SQL Complete users can benefit from the opportunity to view the cell contents in the required format right in the Results grid whenever necessary. The tool takes the pain out of checking and analyzing tables with multi-format data.

The Data visualizers feature that is tailored to suit the needs of the most demanding SQL developers allows viewing data in 9 common data formats: Hexadecimal, Text, XML, HTML, Rich Text, PDF, JSON, Image, and Spatial. To start working with Data Visualizers, you should just output the required data to Results Grid and then switch to Data Viewer.

Image view

The Image View option allows viewing database image contents right in the Results Grid which significantly saves your time and efforts. To enable the Image View, click the corresponding icon on the toolbar.

Viewing data in the hexadecimal format

In case you need to view the cell values in the hexadecimal data format, click the Hexadecimal View button in the Data Viewer and Editor toolbar and enjoy the quick result.

Viewing data in the Text format

To display the table data in the Text format, use the Text View option. It might be very helpful when working with tables that contain long descriptions and text data.

Viewing data in the XML format

XML View is designed to show the cell contents in a popular XML format. Just click the button and see the result.

Viewing data in the HTML format

Switching to the HTML View will display the cell contents in the HTML format in a flash.

Viewing data in the Rich Text format

The Rich Text View allows viewing contents in the Rich Text format with one click.

Viewing data in the PDF format

With the help of Data Viewer, you can quickly view a PDF document just by selecting the PDF View option and then positioning the mouse pointer over the value in the grid.

Viewing data in the JSON format

To view the data in the JSON format, click the JSON View button on the Data Viewer and Editor toolbar.

Spatial view

You can now view data representing spatial values much easier than ever before.

Saving cell contents to a file

Having viewed the cell contents in the format you’ve chosen, you can then save it to a file and SQL Complete will automatically suggest you save it in the format you’ve worked with.

With the kind permission of Devart. Originally published at https://blog.devart.com/get-the-most-from-your-data-with-sql-complete.html

Bulk Uploading To A MySQL Database: A Quick Guide

Kate Naylor — Wed, 18 Mar 2020 15:21:14 +0000

I recently got this question from a friend of mine, and the first thing I thought about was dbForge Studio for MySQL. So, I quickly wrote a detailed instruction, and posting it here, as it may be useful to someone else.

First of all, let’s consider a set of files. These are sourced from the City of Los Angeles Open Data Portal and contains anonymized data on service calls made by the public to the Los Angeles Police Department.Each file contains a year’s worth of data.

Each file has a set of fields:

A unique incident number
Area where the incident occurred
District where it was reported from
Date of dispatch
Dispatch time
A call type code
Call type description

We have created a table called LAPD_Service_Calls with these fields in a test database:

To load a single data file, we can right click on the table and select “Import Data…” from the context menu:

This starts the Import Data wizard. We select the CSV file option and choose the source file and then click “Next”.

In the “Destination” screen, we have the database and the table already selected, so we click “Next”:

In the “Options” screen, we are not changing any of the options already selected for us. dbForge Studio for MySQL is smart enough to read the file, determine the header row, field separators etc. We click “Next”:

We leave the “Data formats” screen as is, and click “Next”:

In the next screen, we accept the mapping already defined and click “Next”:

In the next screen, we accept the append-only and bulk insert mode of loading and click “Next”:

In the “Output” screen, we keep the option to import data directly into the database and click “Next”:

In the “Errors handling” screen, we select the option to abort the load after the first error:

We can now click the “Import” button to start importing the selected file. But we don’t want to load a single file - we want to load all the files.

Most MySQL GUI tools will not have an answer for this scenario - but dbForge Studio for MySQL does. And this is possible through something called a “template”. Basically, a dbForge Studio data import or export template is a file that holds all the options you have chosen in the wizard. You can call this template from the dbForge Studio command line tool and pass it each data file name as an input parameter.

To create the template, we select the down arrow key beside the “Save” button in the final screen,

and select “Save Template…” from the context menu. This will allow us to save the options as a Data Import Template (.DIT) file. Here, we are saving it in the same folder as the data files:

Next, we write a small batch file like the one shown below, and save it in the data folder as well.

echo off

set data_path="C:\Temp\Data_Import\*.csv"
dir /b /s %data_path% > load_file_list.txt


set apppath="C:\Program Files\Devart\dbForge Studio for MySQL\dbforgemysql.com"


if ERRORLEVEL 0 @(
echo on
for /F %%i in (load_file_list.txt) do @(
%apppath% /dataimport /templatefile:Load_LAP_Service_Calls_File.dit /inputfile:"%%i"
)
)
del load_file_list.txt

Here, we are calling a Windows directory command to get a list of the source data files and saving the output in a plain text file (load_file_list.txt). We then set the app path to the dbForge Studio for MySQL’s command line utility. Next, we start reading the file. For each source file name and path listed, we are calling dbForge Studio for MySQL’s command line tool and passing it the data import option (/dataimport). We are also specifying the template file to use (/templatefile:). In this case, it is the template file we created before. The final parameter is the input file for the template (/inputfile). We are passing the data file name here.

We then run the batch file from the command prompt. The output will show data being loaded. After several minutes, all files are successfully loaded:

C:\Temp\Data_Import>data_import.bat

C:\Temp\Data_Import>echo off
=====================================================================Devart dbForge Studio 2019 for MySQL version 8.1.45.0
Copyright 2020 Devart. All rights reserved.
=====================================================================

Data import started at: 15/01/2020 10:00:32 PM

Import mode: Append

Importing data from LAPD_Calls_for_Service_2010.csv to table test_database.LAPD_Service_Calls:
916240 rows imported, 100%

Data import finished at: 15/01/2020 10:03:58 PM
=====================================================================
Devart dbForge Studio 2019 for MySQL version 8.1.45.0
Copyright 2020 Devart. All rights reserved.
=====================================================================

Data import started at: 15/01/2020 10:04:03 PM

Import mode: Append

Importing data from LAPD_Calls_for_Service_2011.csv to table test_database.LAPD_Service_Calls:
891376 rows imported, 100%

Data import finished at: 15/01/2020 10:07:23 PM
…
…
…

=====================================================================
Devart dbForge Studio 2019 for MySQL version 8.1.45.0
Copyright 2020 Devart. All rights reserved.
=====================================================================

Data import started at: 15/01/2020 10:33:25 PM

Import mode: Append

Importing data from LAPD_Calls_for_Service_2019.csv to table test_database.LAPD_Service_Calls:
2059515 rows imported, 100%

Data import finished at: 15/01/2020 10:40:25 PM

As we just saw, dbForge Studio for MySQL successfully bulk-loaded data from a number of files into a target table with very minimal effort.

Creating Professional Reports From MySQL Data

Kate Naylor — Mon, 16 Mar 2020 16:02:38 +0000

For this purpose I would recommend dbForge Studio for MySQL. The tool offers a whole gamut of features for developing and managing MySQL databases. However, it also has very powerful capabilities to create professional reports from MySQL data. Let’s show this with an example.

Let’s say, we are working with the MySQL sample database world_x. It has four tables:

We would like to create a report showing details about each country: its name, its capital city, the continent where it’s located and its total population. Along with these, we would also like to list different cities and towns of the country and their populations.

We have written the following query to get all the relevant info:



USE world_x;

SELECT 
    country.Code AS Country_Code,
    country.Name AS Country_Name,
    country_info.Continent AS Continent,
    country_info.Region AS Region,
    country_info.Population AS Country_Total_Population,
    city1.CityName AS Capital_City,
    city2.CityName AS Country_City,
    city2.Population AS City_Population
FROM
    country
      INNER JOIN 
          (
          SELECT 
            doc->>'$._id' AS CountryCode,
            doc->>'$.Name' AS Country,
            doc->>'$.geography.Continent' AS Continent,
            doc->>'$.geography.Region' AS Region,
            doc->>'$.demographics.Population' AS Population
          FROM 
            countryinfo
          ) AS country_info
          ON country.Code = country_info.CountryCode AND country.Name = country_info.Country
            INNER JOIN
              (
              SELECT 
                ID,
                Name AS CityName,
                CountryCode
              FROM 
                city
                ) AS city1
                ON city1.ID = country.Capital AND city1.CountryCode = country.Code
                  INNER JOIN
                    (
                    SELECT 
                      ID,
                      Name AS CityName,
                      CountryCode,
                      Info->'$.Population' AS Population
                    FROM 
                      city
                    ) AS city2
                    ON city2.CountryCode = country.Code
ORDER BY
    2
;

Now, we can use this query as a data source to build a report. To do this, we choose the “Database > Report Designer…” option from the main menu. This starts a data report wizard:

We keep the default option selected (Standard Report) and click “Next”.

In the next screen, we choose our database connection and select the option to run a custom query. Then we click “Next”:

In the next screen, we paste the query we showed above and click “Next”:

In the next screen, we choose the fields we would like to display in the report, and click “Next”:

In the following screen, we choose two grouping levels. However, we will change this later. Once we choose the grouping levels, we click “Next”:

In the following screen, we keep the stepped layout for the report and the portrait orientation, and click “Next”:

In the next screen, we select the “Casual” reporting style and click “Next”:

In the final screen of the wizard, we provide a title for the report and click “Finish”

dbForge Studio for MySQL now generates a report for us, and opens that in a design palette:

We won’t go into the nitty gritty here, but after some changes, here is our report’s final design:

And this is how it looks like in HTML preview:

Now, you may wonder if the report elements provided by the wizard are all you can get. That’s not the case. Here are the controls you can use in your reports:

And here are the alignment options:

There is also a scripting option to respond to dynamic events:

And finally, you can export your finished report to different formats:

You can also send the report in any of these formats via email to your coworkers.

Backing Up a Database in Amazon RDS for MySQL

Kate Naylor — Fri, 13 Mar 2020 15:52:23 +0000

A quick guide on how to back up the sample sakila database with dbForge Studio for MySQL. Probably, would be useful for beginners.

Let’s go straight to the business. Here, we are connected to an Amazon RDS MySQL instance. We are interested in backing up the sample sakila database (which is not large - but we will get to that very shortly). We right click on the database and then choose “Backup and Restore” > “Backup Database...” option from the context menu:

This starts the Database Backup Wizard. From the first screen, we can see the database connection and the database is already selected for us. We can see dbForge Studio has selected a default backup path and file name for us as well. The file name defaults to the database name and we can also opt to append a timestamp at the end of the file name. Also, there’s an option to delete backup files older than a configurable period of time. There are options to compress the backup file and to encrypt it:

With the options chosen, we click “Next”. In the next screen, we can choose if we want to backup only the database structure, only the data or both. In this case, we are backing up both. Note how we can include or exclude different types of objects from our backup. Again, we are including all objects and then clicking “Next”:

In the next screen, we can choose some extra options. Here, we want dbForge Studio to flush the logs to disk before the backup. We can also choose other options (not visible here) like including database users and roles. Once happy with the options, we click “Next”:

The final screen asks us how we want to handle any errors. For manual backups, we can choose the first option: to prompt us. For automated backups, we can choose the third option (abort on first error) and also write to a log file:

Now, clicking on the “Backup” button will start the backup, and it will be a manual backup. Most tools in the market would give you this type of facility.

What sets dbForge apart is that you can save the options you have chosen so far as a command line script.

Why would you want that? Well, backing up large databases can take time and when you do it manually, any client application can seem to have become unresponsive. Particularly GUI tools seem frozen. In reality though, the backup client is waiting for a synchronous operation to complete, sometimes delayed by network latency. Also, you would want a command-line alternative so you can schedule the backup the task.

Instead of clicking the “Backup” button, If we click the down arrow key beside the “Save” button, we will see an option “Save Command Line…”. Selecting that option will open the dialog box below:

From here, you can copy the generated command with all the options and save it as a Windows command file. In the snippet below, we are calling a script file. This script file has a single line, and that line is this generated command. The output is also shown:

c:\Temp>backup.cmd

c:\Temp>"C:\Program Files\Devart\dbForge Studio for MySQL\dbforgemysql.com" /backup /connection:"User Id=administrator;Password=xxxxxxxx;Host=dev-mysql.xxxxxxxxi.us-east-1.rds.amazonaws.com;Database=sakila;Enlist=False;Transaction Scope Local=True;Character Set=utf8" /database:sakila /outputfile:"C:\Temp\mysql-db\Backup\sakila 20200114 0150.zip"
=================================================================
Devart dbForge Studio 2019 for MySQL version 8.1.45.0
Copyright 2020 Devart. All rights reserved.
=================================================================

Database backup started at: 14/01/2020 4:06:16 AM

Exporting (100%): Done


Objects processed: 26
Rows processed: 25020
Backup file: C:\Temp\mysql-db\Backup\sakila 20200114 0150.zip

Database backup finished at: 14/01/2020 4:07:16 AM

As you can imagine, we could have scheduled this command script. This would be ideal for large databases because we would not have to worry about any timeouts or any unresponsive clients.

Visually Creating MySQL Queries With dbForge Studio for MySQL

Kate Naylor — Wed, 19 Feb 2020 16:41:48 +0000

This small guidance will be useful for non-technical users who have limited knowledge of the SQL language and want to explore the data. And also for savvy SQL developers who might be struggling with a particular complex query. As the graphical tool can be an intuitive way to build the query’s logic.

Fortunately, we have dbForge Studio for MySQL whose graphic designer allows us to select fields from multiple related tables and views, use search conditions, build subqueries, insert MySQL functions, create aggregations sort by different orders and so on. And we come to talk about this tool.

Basically, when developing your query visually with dbForge, you can easily switch to the text view to see the SQL text that’s being generated. You can also check the data view to see the data returned. This is great for debugging purposes.

Once happy, you can save the query as an SQL script file, use it as a data source for creating a report or simply save the text as a code snippet.

Here is an example.
We are looking at a simple MySQL sample database called employees. It has a few tables typical of any HR system:

Let’s say, as HR users, we want to check the past employees of the company. We want to see the departments they have worked in. We want to see the job titles they held in those departments when employed, and the time they held each job title.

We can either write an SQL query, or we can click the “New Query” button in the toolbar:

This opens a new query window with an empty palette. We can select the relevant tables from the “Database Explorer” pane to the palette. dbForge Studio automatically shows any existing relationship between tables:

Under the palette, a tabbed dialog also appears. These tabs allow you to select one or more fields, change, insert or delete joining clauses and specify WHERE, GROUP BY, HAVING and ORDER BY clauses:

We will intentionally use a subquery here. We will select a subset of rows from the dept_emp table where the to_date field is not equal to ‘9999-01-01’ - in other words the employee is no longer active. To create a subquery, we right click on an empty space in the palette and choose “Create Subquery” from the context menu:

This opens another sub-tab under the main query tab. The original palette’s tab is shown as “Root Query” and the new palette’s tab is shown as “SubQuery”:

Next, we get rid of the dept_emp table from the “Root Query” palette by selecting it and pressing Delete:

In the “SubQuery” palette, we drag the dept_emp table only. For this table, we are selecting all fields. In the “Where” tab below it, we specify our selection condition:

We double click on the box’s title “SubQuery”, change it to “dept_emp2”.

We then drag the “dept_no” field from the “departments” table’s box to “dept_emp2”, and drag the “emp_no” field from “employees” to “dept_emp2”. The palette should look something like the following:

Next, we select different fields from the tables, create two calculated fields, specify a WHERE condition and order by two fields:

If we switch to the “Text” tab at the bottom of the query window:, we can see the SQL text that was generated:

SELECT
  employees.emp_no AS Employee_No,
  CONCAT(employees.first_name, ' ', employees.last_name) AS Employee_Name,
  departments.dept_name AS Department,
  dept_emp2.from_date AS Worked_in_Department_From,
  dept_emp2.to_date AS Worked_in_Department_To,
  titles.title AS Job_Title,
  titles.from_date AS Worked_in_Role_From,
  titles.to_date AS Worked_in_Role_To,
  DATEDIFF(titles.to_date, titles.from_date) AS Days_Worked_in_this_Role
FROM employees
  INNER JOIN titles
    ON titles.emp_no = employees.emp_no
  INNER JOIN (SELECT
      dept_emp.emp_no,
      dept_emp.from_date,
      dept_emp.dept_no,
      dept_emp.to_date
    FROM dept_emp
    WHERE dept_emp.to_date <> '9999-01-01') dept_emp2
    ON employees.emp_no = dept_emp2.emp_no
  INNER JOIN departments
    ON departments.dept_no = dept_emp2.dept_no
WHERE titles.to_date <> '9999-01-01'
ORDER BY Employee_Name, Worked_in_Role_From DESC

Clicking on the “Execute” button from the toolbar will show the data in a grid in the “Data” tab:

So this is how you can visually build queries in dbForge Studio for MySQL. Needless to say, production grade queries in a database application can be really complex, but once you know the basics, you can easily break those queries down and start building the subqueries in the query builder. Also, we have not touched on other types of joins (the example here showed only inner joins) and other query types (INSERT, UPDATE , DELETE etc.). All these can be set and configured from the query builder.

Cheers!