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.


Comments

Post a Comment

Popular posts from this blog

SeriLog & Application Diagnostic Logging

Image
Show me an application that doesn't log anything and i'll show you an application which is hard to debug. Every application we write should have a healthy sprinkling of diagnostic logging embedded throughout its logic. A trace to show us what's happening on the inside from the outside. In the past, I've hand-rolled my own bespoke logging classes to output either to file, database or simply console. Not any more! The problem has been solved. It's been done. There are libraries out there like Log4Net or Nlog But from now on, I intend to standardise all my C# code to use SeriLog . SeriLog is an example of a library where so many developers have poured in so much time that any home grown library cannot compete. Why waste your time? Just "nuget" it into your project and be done. Move on. Why is it so cool? Because it's simple. And because it supports many, many outputs. They call them sinks and there is a sink for everything as you ca
Read more

OxyPlot Charting Control

Image
The other day, I needed to display a simple time-series chart inside a little tool I was writing. Now, as we know the web has a slew of cool charting controls to pick from including chart.js and amcharts.js which I have used in the past and are great. But those libraries were out of the question because I needed to include the chart inside a WinForms application. Now, in the past I played with the Charting Controls from Microsoft and whilst they are good, that suite of controls has not been updated for an ice-age! So I decided to have a look to see what the landscape looks like for charting controls in the WinForms / WPF arena which is where I came across OxyPlot . OxyPlot is a cross-platform library for .NET and supports WinForms, WPF, Xamarin and UWP (but let's be honest, Who is targeting universal windows apps?). What makes OxyPlot interesting is that their main page says the project is supported by the likes of JetBrains and Xamarin to mention just a few! Thos
Read more

Android, Self-Signed Certificates and OkHttp

Image
Just recently I have been writing a private Android application using the latest freshness that is Kotlin . Of course, like all mobile applications, it makes heavy use of HTTP requests, sending data back and forth between my app and the back-end API. Because this app is private, it will never be released in the Google Play store and the back-end server will not be publicly available on the internet. The server is a private home web server on an internal network (think intranet) and the application will only contact it over my home WiFi. Did you know that Sensitive Data Exposure is still on the 2017 OSWASP top 10 list ? It's currently sitting at the #3 spot. The OSWASP folks say: "Over the last few years, this has been the most common impactful attack. The most common flaw is simply not encrypting sensitive data." So, as a good developer, my next job was to switch my project to use HTTPS/TLS . That's when I remembered Let’s Encrypt which is a free , auto
Read more