A series of posts about data and databases

Databases are a natural way of thinking about how the world works. It is a similar thing with object oriented design.

The world has moved on, but you can go a long way by modelling data effectively and modelling the world using object oriented design. Indeed, starting with models of how we think the world works is vital in understanding it.

About me

I develop in .Net/T-SQL/plsql/MDX to name a few.
Info Rhino Limited

My Blog

Property Platform - WIP

About this and future posts on relational databases and sql

Periodically, I am going to publish a post relating to some of the amazing features of databases. Some of the posts will put out long bits of sql and we will explain what it is doing.

I don't see many posts about databases and sql on DEV.TO and that is a real shame.
Here is a good one https://dev.to/geshan/you-can-do-it-in-sql-stop-writing-extra-code-for-it-lok
and there are a lot by a user called Helen Anderson on here.

After a few posts on databases, hopefully you will be converted on why they can really speed up Time To Delivery on projects.

Software is all about data

The most important thing about any application is the data. Modelling the data an application will use, really helps the design process. User interface design, and usability experience are to do with how users interact with the data they are presented with.
Many times, if not virtually all the time, whilst we want users to interact with our software, many systems run without user interaction at all.

We would be forgiven for thinking that software is all about usability, but taking a jobs to be done approach.

a) Coinbase thinks it is all about adding more screens to their website, to help their users do more trading.
b) I don't even want to log into Coinbase and would rather some software maximised my returns automatically.

Usability is key for user interactions, but we shouldn't forget how much goes on in the back-end.

Despite there being a natural place for most functionality, developers are shunning databases more than ever.

My friend Dave

He is a smart guy. A bit wooden - so am I, but one thing that we shared in common - we can't avoid falling back to the relational database. We can't help but see SQL as a powerful mechanism to think of data in terms of sets and objects.

So, I started working with VB.Net around 15 years ago, and moved to C# around 13 years ago, and realised I knew little compared to other C# developers. The simple reason is, the thinking between coding and querying are completely different. It takes a huge amount of effort to rewire your thinking towards another specialism.

It may be the reason LINQ was created, somebody realised set based access - even though there may be performance hits made everything more logical for coders.

Our pub talk, often entailed hearing about Java Developers who had spent the last month building referential integrity into their application. Or the developer who had a single table of customers, addresses, orders, and products and was doing "reporting" on a regular basis.

SQL isn't an island either

Once you take coding to an advanced level, once you have learned MDX and cube development, you won't enjoy writing long stored procedures with database joins.

Once you can write sophisticated data structures in C#, you may think SQL datasets to be limited, and you would be right many times.

Once you can put more presentation and data manipulation logic inside the front-end, doing this in a database will seem ridiculous.

Why relational databases are vital to most applications

Relational databases are vital because they maintain the integrity of the data. They have built in bags of tricks which trying to attempt in code would be a huge amount of effort. I can list a few;

• Pragma Autonomous Transations or Table Variables inside transacions.
• Transactions to guarantee success or failure.
• Set based processing.
• Automatic maintenance of keys.
• Triggers - frowned upon by database developers, but try recreating the same in code.
• Deadlock management - single version of truth.
• Try writing Windowed Functions in a programming language.
• Referential integrity. # Start expanding your knowledge on databases and relational modelling One of the most striking features of engineers who can't build relational models is how many hoops developers jump through to do things databases does implicitly;
• Parsing and iterating through a JSON object to execute a stored procedure twenty times rather than once.
• Doing extra checks to ensure data was committed rather than using a transaction.
• Not understanding how to return data idempotently - immutability.
• Not understanding how to use record versioning/auditing.
• Flat table structures which will inevitably incur huge performance costs.
• Over-normalisation.
• Inconsistent data due to a lack of 1NF, 2NF, 3NF, 4NF.
• Not using Kimball structures when relational isn't suitable and thus (over-normalising). ## Picking the right tool for the right job I am building a referral program into my platform(s). GDPR is a big deal and will continue to become more important. To be GDPR compliant seems to be an impossibility, but to adhere to their principles should be attainable.

I decided to build the data management function of how data is stored into the database and transactions. This meant my application could focus on arranging and encrypting the data and the database would enforce the rules.

It just makes so much sense to have the data modelled relationally, and use transactions to accept or reject changes. The .Net Core code then becomes a simple execution of a procedure and collection of data. Like in Entity Framework.

There would need to be so much code written in C# which is nothing more than a few tables, a few stored procedures and one function.

A T-SQL example without explanation

ALTER procedure [zip].[GetArchivingDataRelatedToZipGroup] 
@GroupName nvarchar(100) 

AS 

SELECT * FROM 
[zip].[ZipGroup]
where GroupName = @GroupName 


SELECT * FROM 
[zip].[Folder] fld 
where 
exists (
SELECT * FROM 
[zip].[ZipGroup] gp
where GroupName = @GroupName 
and 
gp.GroupID = fld.GroupID 
)

SELECT * FROM 
[zip].[ArchiveGroupToFinalTarget]
tgt 
where exists (
SELECT * FROM 
[zip].[Folder] fld 
where 
    fld.ArchiveGroupToFinalTargetID = tgt.ArchiveGroupToFinalTargetID 
        AND 
            exists (
            SELECT * FROM 
            [zip].[ZipGroup] gp
            where GroupName = @GroupName 
            and 
            gp.GroupID = fld.GroupID 
            )
    )

Thanks

You won't be converted yet, but hopefully, after a number of posts

Written with StackEdit.