In the late 1990s my martial arts coach asked me to help keep track of the gym's members as his current way of doing it with a pen and paper was getting complicated as his business grew.
Saturday 20 May 2023
Tuesday 18 October 2022
I was recently meeting with a client who needed to replace their multi-worksheet-excel workbook (that they're using as a database) with a real world database.
Of course they wanted to not just replace the main worksheet (with complicated formulas and fancy charts) with a web-based application, but they then asked about apps for phones and tables.
In order to explain to them how I would replace this Excel workbook with a real system, I went to google and tried to find a decent looking infographic of a 3-tiered or n-tiered application design.
Unfortunately, there were no real good ones. They looked like they were done by computer programmers, not graphical designers!
Therefore ... I sourced a designer to create one!
It's not too fancy, but I think it does a good job of explaining the basics of linking together a cloud-based database, with an API and then as many different presentation layers as possible.
What do you think?
Monday 10 January 2022
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:
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 NorthwindGOsp_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):
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:
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!
Tuesday 21 September 2021
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
Tuesday 1 June 2021
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!