SQLServerCentral Article

Objects, Relationships, Systems, And Processes


This is a philosophical article based on decades of personal psychological growth due to working with database systems in sqlserver and from reading many books on Systems Theory.  All information here can be generalized (induction) and used in other areas of your life (deduction).  Most DBAs will feel like experts after their initial two years on the job.  The difference between these "experts" and a seasoned professional is the essence of what I am trying to capture in this article.

We live in a space-time reality.  Space is the objects, relationships, and systems while Time is the process of evolution happening moment to moment.  Our individual evolution goes through the viewpoints of objects, relationships, and systems with each one taking the foreground in our thought processes for years before graduating to the next level as the new "truth" and way of seeing.  Process (time) is the constant evolutionary change that is happening individually since objects, relationships, and systems are really just milestones in a continuum of progression.


In the simplest model of an application, a user types into forms on their monitor and the data is transferred to structured tables in a database where it is persisted and perhaps retrieved for redisplaying at another time.  A model that is less abstract and more concrete would include thinking in objects such as server, database, connection, tables, stored procedures, and triggers all handling the storage and retrieval of the data to and from the server to the application computer.  As a DBA grows in their concrete knowledge of connection settings, data types, and T-SQL commands, they are able to reduce bugs and get their tasks done faster.  They may now be effective and efficient and even know more than one way to accomplish the same tasks and consider themselves an expert.  Little do they know, this is just the beginning.

The next logical step is to go from objects thinking to object-oriented thinking.  Those with object-oriented programming experience will immediately know what I'm talking about.  The basic philosophy of OOP is that each object has many attributes and methods.  The dramatic switch in "seeing" a database table as an OOP object will change how you think and program.  Instead of seeing indexes as separate objects, you will see them as attributes of a table.  You will see primary keys, alternate keys, and foreign keys as required attributes on a table.  You will see standard generated stored procedures as methods on a table that handle security and parameter checking.

I'd like to give you some things to think about concerning an OOP philosophy at the column level.  Objects in the manifest world appear as limitation while the unmanifest appears as possibility/infinity.  In other words, a computer has the capability to run infinite as yet unwritten programs.  Right now a computer can run a written program but that program must be completely limited down to how it has been written in order to be useful and predictable and limited in its output to that which is expected.

Let's look at a column and how we can make it more limited and thus more useful.  First off let's start with a "Comment varchar(5000) null".  I see it has a name, a system type and max length and a nullability setting.  Immediately I assume it has ANSI settings and a collation and language setting too.  It may inherit some attributes from an encompassing object, the table, such as column number or inclusion in a key.  For many more built-in attributes see sys.syscolumns for items such as default value, status, and if it is computed or not.

We can add attributes to our column through custom validations to further limit it and make it more useful.  Using triggers and stored procedures we can make our column read-only or write-once or updateable only under rare circumstances.  We may choose to null out the value since only a row object may be deleted.  We may choose to limit the characters accepted for storage to numbers or letters.  We could hash or encrypt the value for security and legal purposes.  If we get really fancy we could store metadata or scripts in our column which could be executed.

By right-clicking on our column in Object Explorer we can see more properties, many of which are read-only such as Deterministic or In Replication.  We can see any defined Extended Properties.  Some potentially useful extended properties could be an Edit Mask or Display Format or Print Format.  Other extended properties could be defined in order to drive a code generator.  I'm not a fan of extended properties but they are a good object-oriented implementation of extended attributes for columns and other objects.

The object-oriented features of a column are also mostly available to other objects such as tables, triggers, indexes, and stored procedures.  Some of these can be seen as attributes or methods of a column object and some can be seen as objects which are containers for column objects.

When defining new tables and columns it is fine to start with an object viewpoint.  As a dialog with a customer progresses you will be ready to address all the advanced, finer attributes and methods required to meet their needs if you are object-oriented in your thinking.


Mastering the nuances of the multitude of options of all T-SQL statements and commands takes many years.  After gaining mastery, your awareness is freed up to notice certain other things.  In particular, you notice that tables, triggers, stored procedures, and other objects you've defined are just that: static objects.  Basically, objects are the dead "nouns" or definitions in the database.  You could shutdown the host and your database nouns would still be there in perfect form.

The alive part of the database is the "what" that is happening right now.  The aliveness is the executing procedures, network traffic, and DML statements taking place in this moment.  These are the "verbs" of the database.  You can see evidence of verbs by looking at your table sizes.  The largest tables will be transactional and chronological in nature.  For example, debtor and creditor tables may each contain a thousand times less rows than a purchases or payments table.

Graduating to a relationships focus requires a radical change in perspective that has to be earned.  One cannot fully "see" relationships until after mastering objects.  There is a vector to psychological growth, a one way path.  This is experiential.  Rejection of nouns in favor of verbs as the new truth must take place.  This carries over to your social life and you "see" the static person object as an overly-simple definition.  You recognize it is only their actions and behaviors in conversations and crises that truly define who they are.  You begin to see that they are their relationships instead of their physical attributes.

Getting back to databases, you begin to see the dynamic parts of the system such as changes in memory and disk usage as what is important.  You notice queue lengths, dead locks, and inefficiencies.  At this point you may get into running performance monitor, task manager, profiler, and other tools that give you insight into the current activities causing problems due to the relationships of objects and events.

At this stage the DBA becomes aware of design patterns.  Codd's rules of normalization provides a strong foundation but one soon realizes there are alternatives.  Sometimes the fixes are specific and simple such as reducing transaction duration through the use of Service Broker.  Sometimes the fixes are very custom such as recalculating stale results throughout the day using Sql Agent jobs.

Focusing on relationships is a focusing on the "now" and realizing that what is occurring now is all that is important.  In fact it goes beyond that to seeing that the past doesn't matter and the future never arrives.  We can do planning now, but it is the quality of our actions now that improves the plan.  Time is just a model; it is not real.  How the system runs now is all that matters now.  It is always now.  You will never live an instant that is not now.


A relationship between a couple objects is likely a subset of a system or a complete but simple system.  At the other extreme we could say there is only one system which is the entire universe.  We'll create artificial boundaries to see systems as islands, relatively separate for the purpose of analysis.  One can "see" systems only after seeing and learning about relationships and subsystems.  The progression is similar to grade school where we first learned letters, then words, then reading sentences and stories.  The order cannot be reversed.

The simplest "dumb" systems contain inflows, stores, and outflows.  For example, a lake will have inflows which are rain, springs, and a water table.  A lake will have outflows such as evaporation, a dam, or a municipal pump.  The lake with its varying water level is the store in the system.

A feedback loop is at the heart of all intelligence.  When systems have feedback loops they become dynamic and sometimes unpredictable as Production DBAs may have experienced.  Feedback loops can be re-enforcing or stabilizing.  In our lake example, during droughts the municipality will be pumping out more water causing the level to drop faster and faster.  As the lake's water level drops, the water table will feed into the lake and tend to stabilize the level.

Starting with SQL Server 2008 we have a resource governor.  It was an evolutionary recognition by Microsoft that re-enforcing loops needed to be controlled to preserve the robustness of the system.  Initially we deal with these problems by scheduling tasks as sequences instead of in parallel.  We don't run backups during mass updating processes.  We separate OLTP systems from Reporting and Analysis systems.  We limit the growth of tempdb to prevent runaway queries from affecting other systems on the host.  To see systems is to play well with others and be socially responsible.

Some of the more modern additions and enhancements to SqlServer have been services such as SharePoint integration, Replication and FailOver, Security and encryption, Analysis Services, and Integration Services.  All these services provide stability and power to the database system.  One can use a system or subsystem without "experiencing" systems.  But, it will be the old veterans that are walking around and "thinking" systems constantly.

On the personal level we deal with several systems, all of which are important for our survival and happiness.  These systems are our health, wealth, social relations, education, recreation, security, and psychological growth.  You'll notice these attributes can be generalized and seen in all human systems such as family, civic organizations, and government.  At home the seasoned DBA will recognize their child as an individual (object), as relationships with each family member, and as a subsystem of the family itself.


While you are working you are learning new techniques and gaining new insights.  I would actually reverse that last sentence and say that the work is secondary to what is really happening.  You are being changed by the process of performing work.  You are continually evolving.  This process spans all levels of development.  It is why there is no space without time.  Space-time cannot be separated without both illusions dissolving.

Once you have gone through these levels it is easy to see where other co-workers are in their evolution.  The responsibility of someone at a higher level is to talk at the level of someone lower so they will understand you.  Help them from where they are - not from where you are.  It is also possible someone is higher than you and you'll know that because you won't see or understand their point of view, perhaps because you don't speak systems language yet.  Refrain from any value judgments of people.  Everyone is doing the best they can with what they have.  Fitness for a task judgments are fine; you don't want to ask too much or too little of someone.  This is not about ego but about seeing and experiencing more of reality personally.

Allow yourself decades to go through these stages of development.  As systems developers we are lucky to deal with these models and personally benefit from them.  Most people in society will never go beyond the first stage of objects.  At the other end of the spectrum, there are levels beyond systems thinking but only a few pioneers are charting the territory.  Some key words of future stages are Emergent Properties, Dependent Arising, Thesis to Antithesis to Synthesis, Holarchies, Integral, and Holistic.  The fastest way for you to evolve is to become aware of your biggest belief and then try to prove it wrong.  Would that belief be cursors, RBAR, third normal form, a particular design pattern, a monitoring technique, a resistance to a colleague's solution?

Wikipedia has plenty of information on Systems Thinking and Systems Theory.  And below I include links to three books that were helpful to me.  Good luck in your professional careers.

Thinking in Systems: A Primer by Donella H. Meadows

Permalink: http://amzn.com/1603580557

The Logic Of Failure: Recognizing And Avoiding Error In Complex Situations by Dietrich Dorner

Permalink: http://amzn.com/0201479486

Critical Thinking: Tools for Taking Charge of Your Professional and Personal Life by Richard Paul

Permalink: http://amzn.com/0130647608


4 (7)




4 (7)