Tag Archives: Database Design

Abstracting and recombining all the way to the bank

In the past I’ve never understood what people really mean they say “think outside the box” but Jim Harris, in a recent OCDQ blog post, helped me figure it out.

Mr. Harris ends with this provocative line: “the bottom line is Google and Facebook have socialized data in order to capitalize data as a true corporate asset.”  The post starts with a cold war analogy and proceeds to describe how Facebook and Google have made big money as “internet advertising agencies:” offering free services with which users (like us) serve up personal data in return for use of the service, then selling advertising space based on our data (hopefully anonymized).

Continue reading

Get an early start for on-time data modeling

I’m a data modeler, so I enjoyed Jonathon Geiger’s recent article entitled “Why Does Data Modeling Take So Long”.  But why does he say it like it’s a bad thing?

Mr. Geiger’s bottom line is exactly right: “Most of the time spent developing data models is consumed developing or clarifying the requirements and business rules and ensuring that the data structure can be populated by the existing data sources.”  On the projects he describes, no one took time before modeling to determine available data sources and identify business entities of interest, relationships among them, and attributes that describe them before database design started, so the data modeler had to do it.

Continue reading

Use conceptual data modeling in requirements definition

I’ve often thought that conceptual data modeling was an underused tool in the arsenal available to requirements analysts, and in a recent conversation I found that many were surprised that it would be used in the requirements phase at all. Checking the Business Analysis Body of Knowledge (BABOK) I found data modeling listed among the tools available to requirements analysts to “to describe the concepts relevant to a domain, the relationships between those concepts, and information associated with them.” There’s also Steve Hoberman’s excellent book on the topic, Data Modeling for the Business, an introduction to data modeling aimed at a business audience. Continue reading

SQL Saturday #30, Richmond Virginia, April 10, 2010

Thanks to all who attended my presentations at SQL Saturday on April 10.  Here are the materials from my two presentations:

The Business End of Data Modeling (2.5m powerpoint presentation)

Normalize Metadata For Data Integration Analysis (5.5m full version, zip including presentation and code samples)

Normalize Metadata For Data Integration Analysis (small) (2m reduced size version, graphics removed from ppt file)

Here are some quick notes for those looking to run the Metadata prototype:

The prototype metadata database includes SQL Server 2008 data definition language and data manipulation language (DDL and DML) needed to create the database and populate it with tables and columns from Microsoft’s AdventureWorksDW sample database. It also includes a sample requirements spreadsheet and source-to-target map, and SSIS jobs to load the spreadsheets to corresponding metadata tables. These define fictional requirements and mappings to populate the AdventureWorksDW FACTInternetSales table from tables in the AdventureWorks sample database.

AdventureWorks and AdventureWorksDW are available here: http://msftdbprodsamples.codeplex.com/Wikipage (accessed 4/14/2010)

Cloud databases and business/IT alignment

Today, the foundation of most of our custom-built systems is a relational dbms.  While development frameworks vary, they overwhelmingly access and maintain data in relational tables and columns.  As I write I routinely save this post in a MySQL database, and at work I tend SQL Server applications.  Millions of others develop, use, and extract analytical data from thousands of SQL Server, DB2, and Oracle applications, on servers and networks maintained in-house by in-house administrators. Continue reading

SQL Server Row Level Security @ Richmond Code Camp 2009.1

—–

Update 10 January 2010: Thanks to Gints Plivna for observing that we had not posted the slides to this presentation, here they are: Pretty Good Row Level Security Slides.  – Bob

—–

Thanks to those who attended Saturday’s Microsoft Code Camp (see http://richmondcodecamp.org/).  Here are materials for the presentation “Pretty Good Row Level Security” which I did with Nic Morel, my fellow CapTech Ventures Lead Consultant. Continue reading

No business value in nulls

It seems I’m frequently in conversations about using null to represent a business value.  To paraphrase, say there are credit and cash customers, and there’s a suggestion to set “Customer_Type” to “C” for credit and null for cash.  To data and database professionals this is obviously a bad idea, but it’s not obvious from a business point of view. Continue reading

Grow your own row-level security

Dr. Dobbs Portal

Excerpt from "Protecting Your Data with Row Level Security for SQL Server Databases," March 17, 2009

Data security is not optional in today’s business environment. High-visibility hacking and fraud, Sarbanes-Oxley, HIPAA regulations, and the Patriot Act all reinforce the need to present the right data to the right users and prevent the wrong ones from gaining access. Typically, “row level security” (RLS) is one requirement: to allow or permit access to particular users based on data in a particular database row. SQL Server does not provide built-in row level security. Continue reading

Someone’s integrating your data

Here’s a little-recognized fact about data integration: if you run a business or any sizable chunk of one, someone is integrating your data.

In my professional life I have on occasion suggested data integration efforts.  Sometimes my suggestions have been accepted and sometimes not.  As an IT professional I understand that different managers have different priorities, and in a given business situation sometimes other things are more important for example than having a single, consistent source for all customer records, or making sure production data matches financial data.

But as a customer?  That’s different. Continue reading

Beware the devils in the details of data integration

Excerpt from Illusions, Allusions – Let’s Get Real about Database Design, InfoManagement Direct, October 4, 2002

Excerpt from "Illusions, Allusions – Let’s Get Real about Database Design", October 4, 2002

Much of today’s IT application development – custom or off-the-shelf – involves integrating data from legacy systems, third- party software products and external data sources such as demographics or mail lists.  More often than not, data integration is unexpectedly complex, either due to data quality issues or the nature of the data integration itself. Continue reading