Creating a Master Data Management System in SQL

Other SQL Server Topics
1

Hey all,

A‌‌ few weeks ago I started to create a Master Data Management system in SQL. This has been a live-development type video series.

I‌‌ have a few playlists, one being all of the videos and the others being the 'Days'. Days meaning: in 4-6 hours, on a weekend, this is how much I have done.

I think I am at a point where sharing these videos is more meaningful. This data management system has security, historical recording, procedurally generated tables, views, procedures, functions, and the comments and descriptions in this system are ripped out and displayed on a wiki (019). There are a ton of abstract, multi-tenant and hierarchical design patterns throughout the system but trying to explain a sphere is very difficult.

T‌here are only a few more 'days' left before I create a parallel series showing how easy it is to allow Adventure Works and Wide World Importers to operate in the same system - using the same tables for like subjects.
‌‌
I cannot post links, so... searching youtube for
From the Beginning - Master Data Management in SQL
would get you there.

H‌ere are some short descriptions of what the videos contain.
D‌ay 1:
0‌01: Creating Normalized Databases (64 databases)
0‌02: Creating Normalized Files (512 files)
0‌03: Defining Data (User-Defined Types)
0‌04: The First Shape (CFT-B, for Reference data)
0‌05: Normalizing Table Creation (Subjects)
0‌06: The Database Subject (filling tblRefDatabase and its Class, Family, Type)
0‌07: The DataSet Subject (filling tblRefDataSet and its Class, Family, Type)
D‌ay 2:
0‌08: The DataPoint Subject (filling tblRefDataPoint and its Class, Family, Type)
‌009: The Second Shape (RCFT-R, for Relationships)
0‌10: Synonyms for InterfaceMain (One database to rule them all)
0‌11: The SpecificDataSetNumber (Taking back OBJECT_ID())
0‌12: The Process Subject (filling tblRefProcess, tblRefProcessParameters and their Class, Family, Type)
0‌13: Definition Subject - Defaults (using relationships to automatically assign defaults to DataPoints)
D‌ay 3:
0‌14: Definition Subject - Clustered Index (using relationships to automatically create clustered indexes)
0‌15: Views - The Base and Select (the Base and Select views to control data access and governance)
0‌16: Views - Delete, Update, and Insert (the Delete, Update, and Insert views to control data access and governance)
0‌17: Archive and History DataSets (procedurally create Archive and History tables in their respective databases and make the recording of these actions bit togglable)
0‌18: Change Management Procedures (Insert Update and Delete procedures to take control of Change Management)
0‌19: Self Documenting Wiki (using patterns to bcp information and create an informative wiki for checking dependencies, references, and seeing comments)

‌‌Thanks all and happy development.
-‌ Elric

  • I am not selling anything. I am not promoting any product just abstract concepts and design patterns.
1 Like