001 - Creating Normalized Databases - Master Data Management in SQL

NEW SERIES HERE:
https://www.youtube.com/watch?v=TmUrH8C9vus&list=PLPI9hmrj2Vd_ntg2HACiHYeYl7iRvrgPb

This video is the 1st in the series about creating a Master Data Management System in SQL.

In this video I'm going to be creating the databases to store all of our tables and functions and procedures and I'm going to also create a total of sixty-four databases before I get started on the rest of the system. You can follow along or just watch, I'm just going to code and comment as I go.

Articles I've written:
http://www.elricsims.com/wordpress/normalizing-databases/

--------------------
I've decided to create a series to showcase the abilities of master data management and how I can program the system to create all the objects that we will need. Views, Functions, Stored Procedures - all procedurally generated for any table that we create – and even procedurally create the tables themselves. I'm going to apply normalization techniques to naming to the databases, tables, views, procedures, and functions that we use… Basically a holistic normalization.

There is so much information that there is really no order to which I can provide it because every object and concept is so dependent on another piece of the system that I would just be talking for days just to explain one little thing. So I think it's best that you know we will just take it one step at a time, from an empty SQL instance. We are going to create our databases, and then our files, and so on and so forth so thanks for watching.

I'm going to prefix all my databases with ‘db’, my files with ‘fs’ for file space, and my file groups with ‘fg’.

I'm just going to change the default cursor global to local and set the compatibility level to above 130 because I use JSON in this system.

Essentially every database ‘purpose’ is going to have four databases to represent different states of data:
Main – The current state of the data
History – The Change Log of the data
Archive – Data that is retired / removed from the system
Snapshot – Data frozen at a specific point in time.

Databases Purposes (16 purposes):

Interface - (dbInterfaceMain, dbInterfaceHistory, dbInterfaceArchive, dbInterfaceSnapshot) - Where all of our code is going to be stored. We need to have a place to store code.

Security - (dbSecurityMain, dbSecurityHistory, dbSecurityArchive, dbSecuritySnapshot) - Where we will have data-driven decisions for security. Every table our system has a Security flag meaning, every row can have security.

Core - (dbCoreMain, dbCoreHistory, dbCoreArchive, dbCoreSnapshot) - The center of the data management system. The Operational Data Store

Display - (dbDisplayMain, dbDisplayHistory, dbDisplayArchive, dbDisplaySnapshot) – where external content will reside. Just like Security, every table in our system will have a Display flag, thus, every row can have content.

Planned - (dbPlannedMain, dbPlannedHistory, dbPlannedArchive, dbPlannedSnapshot) - sole purpose is to toggle records at a specified time. Activate or Deactivate records, like preloading data or campaign data.

Other - (dbOtherMain, dbOtherHistory, dbOtherArchive, dbOtherSnapshot) - The database that's only going to hold development notes and row guids.

Quality - (dbQualityMain, dbQualityHistory, dbQualityArchive, dbQualitySnapshot) - The database that is responsible for cleaning our system and ensuring keys / definitions are met.

Search - (dbSearchMain, dbSearchHistory, dbSearchArchive, dbSearchSnapshot) – A database where we can find any text in our system.

Atomic - (dbAtomicMain, dbAtomicHistory, dbAtomicArchive, dbAtomicSnapshot) - A database that will flatten our crazy model. This is our Type 1 slowly changing dimension.

Output - (dbOutputMain, dbOutputHistory, dbOutputArchive, dbOutputSnapshot)- a place that we can create tables for outgoing data / files.

Strategy - (dbStrategyMain, dbStrategyHistory, dbStrategyArchive, dbStrategySnapshot) – Our type 2 and 3 slowly changing dimension.

Report - (dbReportMain, dbReportHistory, dbReportArchive, dbReportSnapshot) – A database for client reports.

Analysis - (dbAnalysisMain, dbAnalysisHistory, dbAnalysisArchive, dbAnalysisSnapshot) – A database for cubes, R, or any other external analysis engine.

Input - (dbInputMain, dbInputHistory, dbInputArchive, dbInputSnapshot) – A database for objects that do MEET our data management system’s patterns.

Migrate - (dbMigrateMain, dbMigrateHistory, dbMigrateArchive, dbMigrateSnapshot) – A database for objects that DO NOT MEET our data management system’s patterns.



All of these normalized databases have their unique purpose / function and I think by splitting everything up into specific functionality like we do with data normalization then we can create almost a universal data management system, a master data management system.