Monday, 10 January 2022

Documenting the System with Database Diagrams

As a database consultant, it is not uncommon for me to walk into a new business and be handed a task of untangling some "spaghetti".

What do I mean by that? Well, when I used the term spaghetti in the context of my IT work, I refer to a database that is such a mess that it’s impossible to work out the relationships. And this is especially true as most of my clients never created foreign keys within the database and so the SQL Server Management Studio (SSMS) Database Diagram designer can’t map it out.

This is where a tool such as Quick Database Diagrams comes in. This online tool (available with a free option), allows you to map out and document the database as you go. This is something that I find really helps me to learn a new system quickly, as I am very visual and seeing the database relationships at a high level really helps.

There are a few of these free tools available (see this link here for a review on some of them, including QuickDBD -  https://chartio.com/learn/databases/7-free-database-diagramming-tools-for-busy-data-folks/ ) … but what I find makes QuickDBD the best one is that I can directly copy & paste the table fields from SQL Server into QuickDBD

With some of the other tools mentioned there, I had to do it by copying and pasting each field by field … and since tables can have hundreds of fields (and yes, I’ve seen that plenty of times unfortunately) … copying and pasting it one by one can be extremely time consuming!

Here is how to do it:

(Note: for this demo I am using the Northwind database which can be downloaded from here: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/linq/downloading-sample-databases)

Firstly, go to https://app.quickdatabasediagrams.com/  and begin your first diagram.

On the left-hand side there is the schema as a text editor, and the map is on the right-hand side. If you’re experienced with databases (and I assume you are to be reading this article of mine in the first place 😊) then you would understand how the text schema creates the diagram - the capital PK and FK keywords being short for Primary Key and Foreign Key respectively. 

So you can type them in yourself, or …. 
    • Open SSMS and go into the database that you would like to map

    • Type the following into a New Query Editor:
USE Northwind
GO
sp_help Employees

 This will produce a result in the bottom tab. What you need to do now is grab the following section (two columns only):



And then copy/paste that into QuickDBD here like this:



You’ll see the red Xs. This is just because in SSMS there is a Tab character there and you need to replace it with a Space. You should now have this:


No go ahead and run sp_help on these two tables: 

sp_help Territories

sp_help EmployeeTerritories

Then like you did above, copy/paste those two columns (name and type) into QuickDBD. And again replace the Tabs with Spaces so that you have this: 


Finally, we need to do the relationships. 

In the sp_help results, you would have a list of Keys that will help you do this. For our demo we are only going to map these three tables (to save typing) but obviously you can replicate this for all foreign keys that this table relates to. 

Firstly, add the characters PK against each of the Primary Keys. If you have done this correctly, a little image of a Key will appear next to them in the diagram. 

And now to add the relationship, add in the FK characters, followed by a >- and then the target field. If you’ve done it correctly, then you should end up with the following: 



That’s it … you can just keep going now copying and pasting the fields from SSMS using the sp_help stored procedure and QuickDBD will do the rest for you!

Please reach out to me if you need any help!

Rodney 

www.rodneyellis.com.au 









Tuesday, 21 September 2021

What am I paying you for?

Once upon a time ... 

I had a job as a DBA. It was your typical Monday to Friday, business hours gig. 

When I started there, the system was a mess. Constantly going down, slow, no decent backup/restores happening, nor indexing, check DBs etc ... 

After a few months, I got it all under control, and even scored a payrise ... the kind where the boss calls you in and just tells you you're getting a raise because your efforts have been noticed. 

Fast forward a few months ...

The boss comes down to the IT room (on the ground floor, of course ... how stereotypical) ... 

I'm standing around doing nothing, except shooting hoops with the toy basketball hoop. 

He looks at me and says "what am I paying you for?" 

I look at the SQL CPU .. it's low. Backups worked, everything is humming. Did he want me to break something just so that I had something to do. 

That was when I really came to the realisation that DBA work was not for me. I did my job perfectly, but still had to turn up every Monday at 9am to do nothing!

Saturday, 10 July 2021

Import Excel into SQL Server


Currently a Covid Lockdown in Sydney and so I'm bored. Therefore, I decided I'd share some knowledge from all the years of ETL work I've done. 

In this video I show a simple way to get around an annoying "Microsoftism" when doing what should be a straightforward import of data from Microsoft Excel in SQL Server. 

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. (System.Data)

If you've ever tried using the SQL Server Import and Export Wizard, and received that error, you'll know how frustrating it is ... 

Here's the solution: 


Tuesday, 1 June 2021

Can you tell your customers if you moved premises?


In the building next to us is a mechanic. He used to be in a different factory unit, but due to that owner selling, he was forced to move into a less desirable factory out the back. 

When I asked him why he chose this factory to rent, and not one with more exposure down the road, he said it was because all his clients knew that he was in this block of units. 


It still amazes me, and will until the day I drop dead, that people don’t keep a database of their customers.


I know my mechanic has never emailed me. If he moved, I wouldn’t know where to find him! He would lose my family as customers and have to find new ones.


If your business has all its clients’ information stored, even just the basics, then you can email or ring them when you have important changes - like changing address! 


You probably worked really, really hard to build your clientele. Don’t leave them behind when you move. 


Build a database … or better yet, contact me and ask me to build it for you!


Friday, 21 May 2021

Excel <> Database!

Please STOP using Excel for managing your customer data. 

Excel has its place, but that place is not being a surrogate database! 


Last week we took on a new client in Cronulla (a small community based sporting club) who had been managing their attendances with spreadsheets. The poor girl whose job it is to work out who's coming and who isn't was struggling big time. 


We were able to take that data and import it into our Arnold Gym Management System, and now her workload has been trimmed by over 90%! 

Help us rid the world of Excel Spreadsheets being used as databases!




Thursday, 23 January 2020

Arnold Martial Arts Gym Management Release Notes 24-01-2020


*** Arnold Update ***

Another busy week at Stingrae as we release a new update for Arnold Martial Arts Gym Management Software. Here is a summary of the changes, which mostly came about after doing some consulting with one of our main clients (a lot of them sure make things easier to understand, especially for new users):

* Homepage now displays when your subscription is due to expire/renew.

* IntegrayPay Direct Debiting : when a payment fails, the reason why is now also shown in the IntegraPay Batch Report, and on the Payment Details Popup (which has also been re-written)

* Class Maintenance - More logical flow to the maintenance sections and consistency among the titles (Names = Types etc). Bug also fixed where previously you could accidentally save a grade without a style.

* Sales Item - Tax now calculated as you type, without the annoying postback. More popups in this section too to explain the settings further. And the grids refresh on changes, when previously there was a bug where it only refreshed after refreshing the page.

* Incidents - made the screen wider and easier to navigate.
PLUS you'll see even more little temporary popups with handy hints and some question marks with help too.

As always, any suggestions for features, please let us know!

Friday, 6 December 2019

Release Notes 2019-12-07

*** Arnold Gym Management Release Notes ***

Another busy week at Stingrae, with the following minor changes to our popular martial arts school management system:

* Redesign of Member Home Screen so that you can see almost everything at once
* Improved Popup screen for Contacts (previously had display issues on Edge)
* Sections for Notes, Gradings & Classes now stay open when you click on them
* New section to view Competitions for Members
* Fixed Layout issues on Account Sale
* Fixed bug with business logo on Member Portal

https://www.gymdatabase.com.au