MD3 Model Data Patterns

Metadata Driven Database Deployments (MD3) is my build-and-deploy system for SQL Server database applications.  In the last couple of posts we covered what problems MD3 solves and how it solves them, specifically involving stateful database objects.  In this post I'm going to cover how MD3 handles deploying "model data".  I'm not aware of ANY database deployment tool by ANY vendor that has the ability to deploy model data in a "stateful" way.  In fact, I have a few MD3 users who started their journey to properties-based, metadata-driven evolutionary databases by first implementing MD3's model data deployers.  

What is Model Data?

You may be familiar with model data by a different name.  I've had clients that have used these terms as well:  

• lookup data
• allowable values
• system data
• seed data
• master data

I'll use the term "model data" for the remainder of this post, but the terms are interchangeable. A quick example of model data is any data that YOU deliver with your database.  For instance, you may have a table named dbo.StateProvince that holds a row for each state or province in the United States.  This data rarely, if ever, changs.  

Model data may:  

• never change
• rarely change
• only be modifiable by the customer
• only be modifiable by YOU (the vendor)
• be modified by either YOU or the customer.  

Classes of Model Data

Not all model data is created equal.  There are classes of model data based on who may modify the data.  I've invented these terms, they aren't industry standards, but I think by describing model data in terms of its "type" it is easier to understand its requirements for deployment.  

Often your model data is not as black-and-white as the types I've listed above.  It is common for some metadata to be a combination of types 2 and 3.  That's OK, MD3 can still handle it.  

I've implemented MD3 model data patterns for several different ISVs (Independent Software Vendors).  In every case determining the model data type for each piece of model data was the most difficult part of the migration.  Software vendors just don't understand their data I guess.   Don't be surprised when model data becomes the Achilles' Heal of your MD3 migration.  But don't let that scare you.  In the process of getting your model data correct you'll learn A LOT about your database and application.  

Scripting your Model Data

Let's look at how MD3 handles model data.  We'll use Sales.Currency as our example model data.  Out-of-the-box this table contains 105 currency rows.  

Let's start by scripting this model data out using MD3's scripting tools.  We'll cover how to reverse-engineer your entire database in the post [[How to Reverse-Engineer Your Database with MD3]], but for now let's start our look at model data by reverse-engineering Sales.Currency.  

Scripting model data is as simple as running a stored procedure, MD3.ScriptModelData  (code).  Here is the list of all scripting options.  

This procedure simply generates output that can be saved to a .sql file that will contain all of the commands necessary to generate a model data script for the given table that you can then modify as needed for the various "Types" of model data (discussed above).  There are lots of options you can call this procedure with that will alter the output to fit your needs.  You can regenerate your model data as many times as you like until you get it in the correct format to meet your requirements.  Here is the basic command to script Sales.Currency.  Before you execute the script press Ctl + T to ensure your output is in text mode.  

Simply save the output wherever you want to store your model data, preferably under version control.  If you follow the out-of-the-box MD3 patterns then this would be saved to 8ModelData\Sales.Currency_modeldata.sql.  

The Model Data Pattern

Now that the data is scripted, let's look at the model data pattern in detail. The graphic above shows the start of every model data script.  The first thing we do is temporarily disable all FK constraints on the tables associated with this model data table.  We do this so we can load and manipulate the model data without having to execute the scripts in dependency order.  Next we build a temp table that is schematically identical with the underlying table.  The WHERE 1 = 0 clause quickly gives us the structure without inserting any data.  Next we populate the temp table using the data in the table as it existed when it was scripted.  Every row will be scripted.  

The graphic below shows the code after we build the temp table with the scripted model data.  Note that there is an "insert section", an "update section", and a "delete section".  These sections simply compare what is in the temp table vs what is in the actual table.  If a row is found in the temp table that is not in the actual table, by key, then that row is a new row and is inserted.  If a row is in the actual table, by key, and is not in the temp table, then that row is removed from the actual table.  

The "update section" does a binary_checksum against non-key columns then compares that using the key and updates the actual table wherever the binary_checksum differs.  

Finally we drop the table and print a message that we should re-enable all FK constraints.  This is a message that is helpful to the developer.  If using MD3's executor then the FKs are automatically re-enabled and any error messages (DRI errors) will be thrown as normal.  

This is the basic "Type1" model data pattern that MD3 provides out-of-the-box.  

Model Data Use Cases

Assume you have a requirement to rename ZWD's description from "Zimbabwe Dollar" to "Zimbabwe DollarBill".  All you need to do is change the model data entry as shown below.  

You can see the output.  No rows inserted, none deleted, and one row updated.  

Suppose the requirement is to add a new currency called ZZZ FooBar Dollar.  Here is the change:  

Note that when executed we have one new row inserted this time, with no updates and no deletes.  Just what we expected.  

Finally, if you wanted to delete the metadata for ZAR Rand you could either remove that row or simply comment it so you had a "history" of that data element for future reference:  

Type2 and Type3 Model Data

These examples assumed you only needed to handle Type1 (vendor-only modifiable) model data.  MD3.ScriptModelData only scripts Type1 model data. The reason is simple...Type2 and Type3 model data are highly customized and are specific to your given use case.  You need to handle these cases by altering the output from the reverse-generated script.    How can we handle Type2 and Type3 model data?

Type2 model data is quite simple.  In this case the vendor initially supplies "seed data" that is never subsequently modified by the vendor.  We simply take the scripted model data and remove the "update" section.  

Type3 model data is totally dependent on your exact requirements.  I can give you ideas on what needs to change in the reverse-engineered data, but you can probably figure out what you need to do yourself if you fully understand your model data.  I can't stress that enough...you need to understand your data.  After that you simply modify the model data pattern to fit your needs.  

Summary

In this post we covered model data, or data that is delivered with your database application.  I (and MD3) view model data as just an extension of your database schema.  Model data needs to be "versioned" just like your schema.  It is a "stateful" database object, just like a table is.  When we modify model data we need to take care to ensure we've handled all of the historical data that may reference that model data.  The model data patterns in MD3 can be used without implementing any other MD3 features.  Implementing model data in a repeatable manner is by far the most difficult aspect of deploying MD3.  This is because most developers truly do not understand the model data in their applications.  Can the data be altered later?  Can it be altered by a customer?  How do we handle model data that was altered by the customer?  These are difficult questions when you don't understand your data.  Thankfully, once you understand your data it is very easy to script it into the MD3 model data pattern.  

You have just read "[[MD3 Model Data Patterns]]" on davewentzel.com. If you found this useful please feel free to subscribe to the RSS feed.