Data Modelling and Diagramming Database Schemas

So, I have recently been designing a schema for a new database system. As everyone knows, this is a technical process and requires a good understanding of the problem domain to make sure you collect all the business concepts and relationships.

For these kind of things, you can't beat a good entity relationship diagram to visually see the makeup of your database tables and the relationships between them.

This post is about the "Toad Data Modeller" application which is what I've been using to model my database design. It's such a great tool that I think it should be present in the toolbox of any DBA.

Now, I know Microsoft includes a rudimentary diagramming tool in SQL server. Just right-click on "Database Diagrams" and add the tables you want to include.

But this feature hasn't been shown any love since SQL Server 2000 and the diagrams are very simple. Microsoft Visio is another tool I've previously used and it allows you to create an ERD of your database, although I think you may need the professional version to reverse engineer an existing database. It's not intelligent in any way apart from creating a pretty diagram.

But my new favourite tool to use is Toad's Data Modeler.

Below is an example screenshot of Toads Data Modeler on the sample "AdventrueWorks" database.

It allows you to reverse engineer (read in) an existing database, although the freeware version (the one I'm using) only allows you to reverse engineer a maximum of 25 tables at a time.

Once the model has been loaded you can easily add foreign keys between tables, add unique constraints, and basically ensure there are no floating tables in your design. You can hover over constraints to see which database fields they are attached to. If you have made changes to the actual database schema under the covers, you can "Update the model from the database". And if you have made any changes in the app, you can export the SQL.

In short, it's awesome! It's a great tool to use to scan over your tables to make sure all the required relationships of each individual table is met. Oh, and did I mention it works with all the major database systems. And its free!

If you haven't got it download it from here.

Read More

Another Year. Another Year's Checklist!

Ahhh yes… Another year! Another fresh start and set of goals for the year. So many good intentions.

On this blog I try to detail my meandering adventures in technology, so I thought I'd write up the tech-subjects I've had in the back of my mind but never got around to looking into.

You never know, maybe this is the year I can tick some of them off!!

Truth be told, I have had this laundry list of things-to-do-and-learn swirling around my head from last year. And maybe the year before as well.

• C++ 17 will be released this year. I've been keeping up with all the new toys that have been added to the language since C++11. Even though I don't code in C++ anymore (C# is my daily driver) I still have a fondness for C++. It would be good to have a refresher on some of the newer language features.
  
  Although, it seems like C++20 is going to be the BIG release with modules (finally) and async/await billed as features!


• Install and play with .NET Core on Linux and port a simple application. .NET Core 1.0 was released last year and I have not yet played with it. Although in my defense they do say to wait until version 2 of a product. You know, for the bigger bugs to be ironed out.


• Install Linux (again) on one of my machines!! If I am going to test and play with .NET Core I will need to have Linux anyway. Is this the year I finally say goodbye to Windows? I say this every year. And the answer is always no! I love Visual Studio too much!


• Take a look at the Nim Programming Language. There was a time when I wanted to check out Lua. That urge has now been replaced by Nim.


• I have had an idea for a side-project for ages. This project would be a "Windows Explorer IMDB Overlay" over the filesystem and would link to IMDB. This TMDbLib API looks like it will do just the job. Its an API for the themoviedb


• I would like to play around with SkiaSharp and UrhoSharp. They are both graphics libraries. Skia is a C++ open source 2D graphics library and is used in Chrome, Firefox, Android etc. SkiaSharp is a C# port. Urho3D is a cross-platform 2D and 3D game engine. UrhoSharp is a port to C#
.

• Pay some attention to my Amazon EC2 instance. I have a server idling in the sky and it would be good to do something useful with it.


• Think of an IoT project to do on my Raspberry Pi 2 device. I did have an idea to try and link up my go-pro to it. I did find a website that detailed the go-pro wifi commands and thier general query structure.


• Play more xbox! (Why isn't this top of the list?)


• Read up on algorithms.


• Read the book CODE by Charles Petzold. It's an old book but it's supposed to be a nice walk through of the basic concepts of computers. I've been meaning to read it for ages now.


• That reminds me! Read more books. I already have an Audible account but I need to set aside more time to physically read. You know. The old-fashioned way. :)


• Re-Install and play with Gimp and Blender (...Again, hopefully this time I will grok them)


• I've been meaning to look into Vue.js and Ractive.js. They are both light HTML templating engines and they both use moustache style templating. They both look good and if I ever do a client-side web project I hope to look into these.


• Play around with PostgreSQL.. I use SQLServer daily and am very adept with it but I have never used PostgreSQL which is just as capable. There is a windows version of the database here. I can't believe the download is only 137MB. In comparison, the download for SQL Server Management Studio (just the interface) is knocking on a gigabyte!


• On a side note (while were talking of databases), try to think more "set based" than "procedural based" when writing SQL queries. Why do I always think in cursors instead of set based operations? Damn my imperative mind!


• Finish off my Xamarin side-project and post it on my GitHub. Or at the very least post what I have done so far.


• Python. If I need to knock up a quick throw-away script (say to do something with the filesystem) I want to try and remember to use Python (just to keep my toe in).


• Take another look at TypeScript now its reached 2.1! Version 2.1 now compiles async/await code down to EcmaScript 3 without needing Babel! Yay!


• Take a look at the Kotlin Programming Language! Apparently you can write Android apps in it now!


• Unity can run on all platforms but can you use it for a line of business app? Probably not, but the scripting language they use is C# and it would be interesting to dig into.


• .NET Standard 2.0 is due to come out this year (apparently when Visual Studio 2017 drops). I deffo want to play with this. Is it the future of .NET?

So there it is. In short, I seem to have a lot of spinning plates of "things I hope to get around to do/read up on/investigate". There’s a lot out there to learn. I guess that's one of the reasons I have a not-to-do list!

Read More