Transactions and References

As I speak with people from various technical backgrounds, I find there is lot of confusion about how to structure data.  For a full background on this and related topics I rely upon the work of Fabian Pascal, C.J. Date, and C.F. Codd, vibrantly maintained on Pascal’s site,  dbdebunk.com.  I encourage anyone who favors “learning” over “training” to spend some time there.

When I address a new challenge of database development or workflow design, I start by distinguishing between Transaction data and Reference data.  Transaction data are those that accumulate over time, but do not change frequently.  There are a lot of INSERTs, but not many UPDATEs.  Orders, invoices, calls, observations, and events are all types of Transaction data.  Transaction data may include attributes that contain Reference data.

Reference data are those that remain mostly static over time, but once established, change infrequently.  Examples include addresses, phone numbers, product lists, clients, vendors, etc.  Reference data grows at a much slower pace than Transaction data, so much of the maintenance involves de-duplication and consistency of form.  For instance, address data can be better de-duplicated if at first you make all the street types consistent: i.e. “Avenue” and “Ave” and “Av” are all made to be the same “Ave”.  If you can do this BEFORE inserting it into a database, that saves A LOT of time and resources.

By contrast, Transaction data maintenance is more focused on removing data that are no longer needed.  The most efficient way to manage Transaction data in a database is to 1) not load attributes that will never be used, and 2) partition data based on attributes that reflect its useful life.  This makes it easier to remove it without scanning all the data you need to keep.

So the next time you are thinking about loading up your linked lists, name-value pairs, and dictionaries into a database, remember that you only need to save what you will use.  Kind of like your refrigerator.  If you are not going to eat it, you don’t need to wrap it up or freeze it.  Collect only what you need.   Determining what you “need” is of course a prerequisite.

Database Design is Easy — Or Is It?

I came across a Request for Proposal from a government entity whose sole purpose was to conform to the data collection standards of a superior government entity.   The following entry is paraphrased for brevity.

Yes/No Questions. The “yes” response and the “no” response each has its own column in the spreadsheet tool.   To specify “yes” for a given question, the user should enter a “Y” in the given question’s “yes” column. To specify “no” for a given question, the user should enter an “N” in the given question’s “no” column.  One question on the form is an exception to this “standard” where there is only one column in the spreadsheet tool for this question, and users should enter a “Y” for “yes”  and “N” for “no”.

This was written by professionals who should be in a different profession.  I struggle with how to explain why this is fubar.  So here goes.

First of all, these are questions on a form, questions, mind you, with only two answers, “yes” or “no”.  Really.  How complicated can that be?  Apparently, very complicated.  If two responses (one in the Yes column and the other in the No column) are allowed, how will one know which response reflects the facts of the respondent?

The noted “exception” should actually be the “standard”.   Most likely, this was written by lawyers to allow for flexible interpretation of the law when enforcing compliance so as not to hurt anyone’s feelings.  Or maybe it exists to confuse the respondent, so they will give up filling out the form entirely to seek out other sources of assistance.  Whatever the reason, the person designing such forms should be in a different line of work because either their brain or their heart are not in it for the right reason.

Why is this important?  Say you wanted to report on the number of respondents who replied to a particular question.  Count the ones who respond “yes”.  Count the ones who respond “no”.  Simple, right?  OK.   Now calculate the percentage of respondents for each.  Well, if some put down “Y” in the Yes column AND “N” in the No column, the sum of the counts of each would be more than the total number of respondents.

Now, image you are a legislative leader and you are reading a compliance report based on these statistics in order to place a vote for a multi-billion dollar extension of the program.  You might see that 65% said “Yes” and 63% said “No” to a particular question on which you would like to base your decision.   Or maybe, the data analyst preparing the “required pie chart” report will recognize the data problem, and fudge the responses so the chart reflects HIS political preferences.   Democracy in action.

I have all the answers, its knowing the right the questions to ask that is the challenge.

For more examples of this kind of thing, please visit my favorite website, www.dbdebunk.com.

I Want to Own my Own Software — Really?

Here is a plea from a small business owner who wants to own their own software:

“I had a programmer, who is no longer in the picture, develop software for me.  He placed the program on his server. I can use the program but, I cannot register others to use it, etc. I need someone to put the program on MY server where I have control.”

OK.  So you own the intellectual property rights to some code that helps your business succeed.  But what if your business needs change?  You still need someone to take responsibility for understanding that code, have the skills to modify that code, and be interested and compensated sufficiently to continue to support that code.   If you own it, now you are in the software business.  You need to keep an employee or a freelance web programmer interested in your business application long enough to survive the next operating system or framework upgrade, business process change, or hardware upgrade.

What if the framework vendor decides it no longer wants to support that framework?  Vendor acquisitions, product sunsets, or management changes usually result in someone (or a lot of someones) rewriting the same code over again every 3-5 years.   So now your precious intellectual property is made obsolete by forces beyond your control, those external forces operating behind the scenes that only seasoned IT executives understand all too well.

But you just want to run your business as efficiently as possible.  A software package made for your industry seems appropriate.     That is, until that vendor gets gobbled up by a competitor or makes upgrades to features you don’t use.  Again, you are back to  rebuilding into code your unique business process–your trade secrets–that make your business run efficiently.

Standards.  Yes, the answer is standards.  But what is the standard today?  When and how will that standard change over time, and will that affect your application development? This is a moving target that is at the bleeding edge of the software field.  For business applications that process transactions, does this really need to change as often as software vendors churn out new features?  Why should your IT support persons change more often than your accountant?

Business computer systems serve two purposes.  They keep track of Things and they keep track of Events (i.e. transactions).  For example: “Sold 34 Widgets on Thursday”.  “Widgets”=Thing.  “Sold 34 on Thursday”=Event.  Not a lot has changed in 40 years with respect to the technology needed to handle this.  HTML behaves like CICS.  Relational databases replaced ISAM.  The software industry’s penchant for “new and improved” is why spreadsheets run half the world.  But I digress.

If you focus on the SERVICES related to keeping order among your chaos, you will likely succeed with less technology pain.  Like having a good CPA keep track of your books or an ATTORNEY keep track of your contracts, having a good professional keep track of your software development will likely make you more successful, measured in your preference of either more profit or more free time.

So what is this type of “professional” called?  There isn’t one name for such a person.  There are no well known licensing boards certifying information technology professionals.  In fact, if you asked a hiring manager (or professional recruiter for that matter) what to look for in an freelance web programmer, they would list a litany of products, technologies, and programming languages.  If carpenters were hired like programmers, the interview would go something like this.  If you read that link, you get my point.

Bottom line, technology matters, but only if you have the professionals who can sift through the pile and find your pony in there.

 

What is a Software Application?

In 1983, as an analyst for a respected information technology industry marketing research organization1, I once had a debate with another analyst on how to classify a database management system.  Was it “application software” or an “operating system”?

I argued that applications, or at least the processing logic of software applications, were being built in the database language, SQL, and this made it part of the application software arena.  My counterpart argued that database management systems controlled access to the disk hardware to the extent2 that no file system was required.  I said that the “file system” was separate from the “operating system”3, could possibly be considered “application software” by itself, and so on, and so forth.  I lost that argument in 1983, but I think today an analyst would be hard-pressed to succeed in convincing anyone that a database was an operating system.  In fact, today’s virtual software platforms turn present day “operating systems” into “application software”.

IBM’s VM operating system was just beginning to take shape in 1983.  Today, most companies are relying on VMWare to run their businesses and not looking back.  Desktop operating systems are even being “deployed” via VMWare to terminal emulators, now called Internet browsers.  How the evolution of CISI became Chrome today was a world-wide cooperative effort.

But is the Internet browser dead? Apple’s venture into the touch-screen user interface powered by client-based code, and Google’s foray into the same architecture with Android, has brought the “fat client” back.  With Apple it worked because they controlled the hardware and limited the user interfaces to a small number of dimensions.  Android has since blown that away in market share and a plethora of screen dimension choices and accessory options that create insurmountable cross-platform testing challenges.

There is a New Hope.  After ten years of existence, HTML5 and CSS3 are finally getting some attention. These two new standards bring back the Internet browser to the application software domain because they support a wide variety of user-interface controls, while providing greater control over adaptation to displays.  This forethought occurred before the invention of the iPhone because such is the evolution of software.

To stay ahead of these changes, reduce your ever-redundant software development efforts, and truly empower your staff to seek out new applications, to boldly solve the backlog of end-user demands, and breath new life into your business processes, you need to rethink the Internet browser as the primary user interface for your applications.

Konduit provides custom application development and workflow design delivered over the Internet that combines all IT services: database development, hosting services, data stewardship, and end-user support into a single plan.

Footnotes:
1 INPUT (acquired in 2010 by Deltek, Inc.), was bigger than Gartner Group at the time.
2 Pun intended
3 The file system is an API to the disk like shells are user interfaces.

There is Nothing "Standard" About Software

The Holy Grail of application development is a tool that empowers the individual to “build it themselves.”  Spreadsheets, WordPress, and SharePoint seem to lead in that space.  There is no shortage of application development tools.   You can search the Internet for application development tools only to discover that most solutions are so focused and simplistic, they hardly do anything.  You can spend six months to learn how to build your application, only to spend the next six months upgrading it for the next release, until the vendor “sunsets” your platform to make way for the Next Big Thing.

To make software useful requires people who understand your business.  Unfortunately, staffing or finding a freelance web programmer who knows the particular language, libraries, or tools that your application is built upon is no small task.  This is made even more challenging by the ever-improving offers by old and new vendors.  Technical recruiters and HR people  struggle with defining the skills needed because there are literally hundreds of ways to build a business software application.  There really are no “standards”.

What businesses are lacking is standards, real standards.  Imagine what software industry “standards” would read like if they were applied to the construction industry.

Pipe Fittings.  Available in a variety of materials, you can connect your toilet to your water source by configuring your tank prefilter with any number of other Open Source materials.  After extruding the material through your 3D printer and threading the connectors with just about any threading tool, you can form the outer casing to fit your team’s own wrench set.  For a fee, we can provide you with our wrench set.

Wall Components.    We give you everything you need to build your own house.   Solid Pine logs in standard lengths can be trimmed to any dimension using freely available Open Source band saws.  They can easily be bound together with your choice of off-the-shelf components including rope,  tar, steel shanks (headed or headless), and leather trimmings.

Electrical Wiring.   Our professional installers can get you up and running in no time.  We work in one metal (copper, aluminum, brass), use only certified Acme parts, and will give you copyright and patents on every bend and connection made throughout your house.

In my article “I Want To Own My Own Software — Really?“, I reference an mock interview dialog called “If Carpenters Were Hired Like Programmers“.

 

Freelance Web Programmer for Small Business

Marketing your services as a freelance web programmer is a tough gig.  But doing so in the United States Software Development Market has become increasingly more challenging.  Optimizing your website for search engines, advertising through Google or Facebook, and sending countless postcards and emails are just no match for the well connected and HR-focused efforts of recruiters.  The old adage “it’s who you know rather than what you know” that drives your success is still true.  But as a provider of application development services, getting you to know me is more than half the battle.

Technology recruiters are extensions of large corporate HR departments.  They work their network of buyers and sellers to earn their healthy markups.  HR Staffers specialize in labor law.  Technology recruiters find candidates with specialized skills.  The problem is, if you are a small business owner without a techno-priest advocating for a specific set of skills, you are left with thousands of technology choices and nowhere to turn for a solution to workflow design and application development.  Oftentimes, a power user or student will have provided a “temporary solution” in Excel or Access that is difficult to support as the business requirements change.

I’ve worked for plenty of recruiters, but recent experiences have demonstrated that disintermediation by web-based freelancer sites like Upwork, which acquired Elance and oDesk, and Freelancer.com are attempting to take market share from sites like Dice.com and Guru.com, who have been around much longer and are more supportive of recruiters.  New entrants like Toptal are trying to replace recruiters entirely by claiming to have vetted their applicant’s skill sets.  Unfortunately for them,  recruiting is a local business founded on years of developed relationships.

I don’t know if recruiters ever get calls from small business owners looking for help.  But if they do, I expect that their lower budgets and inability to sign for a minimum engagement of three or more months would prevent a deal.  So the small business owner has to ask a friend, their desktop support provider, their PC/Printer/networking retailer, the local Small Business Association chapter, or local technical college (collectively “small business associates”) for a referral.

What if any of these small business associates were able to refer someone who can help them with a small project and is available within three times zones, can provide continuous support, and costs a mere fraction of the cost of hiring a full or part time application developer?  That is what Konduit does. And we are willing to share with small business associates some of this continuous micro-revenue stream for simple referrals that result in new subscribers.

To give people a sense of the types of Transaction Editor (TEd) projects we at Konduit excel at, I have provided on my website TransactionEditor.com a random display of the descriptions of projects we are bidding on.  You can find this at the bottom of the “Portfolio-Your Application” page, under the heading “Posted Projects (RFQ,RFP) Suitable for TEd“. This helps our referrers understand the scope we are focused on.  This should also help small business owners determine whether we might be able to help them.  Along with the description, I rate the quality of the mostly unaltered description as Good, Better, Best:

  • Good = Vague description, but function-related rather than skillset-related.
  • Better = Majority of functionality within TEd capability, but some items require clarification or work-arounds.
  • Best = Well documented functional description of requirements.

With over 20 years of experience, my services also include database development, application development, and workflow design.

Database Awakenings

I started my information technology career after earning a degree in Economics in 1982, primarily by earning A’s in Econometrics.  I just found statistics to be simple to understand and focused my curriculum on a variety of Liberal Arts courses.  Near the end I had more Economics credits than any other college, so a B.A. in Economics set me free.  The Personal Computer revolution was just beginning and the cost of computing was becoming affordable for small businesses.  An Apple II with a C/PM card would get you VisiCalc, WordStar, and dBase II which could easily justify the expense.

My first gig as a freelance web programmer preceeded the Internet and amounted to teaching myself Applesoft Basic so I could deliver a Quality Control application to Northridge Hospital in southern California.  The Surgical Unit nurses who reviewed the medical records wanted to show physicians the rate of failure (death, relapse) grouped by surgical procedure used, and of course, by physician.  So I started to read, and read, and read, about data normalization and relational concepts.  E.F. Codd’s work had been continued by C.J. Date in several volumes of papers collected in his published works.  My comfort with reading academic journals was honed by my Econometrics professors.  Currently this important work is continued by Fabian Pascal, whose website is my all time favorite: dbdebunk.com.

Soon after that I took a regular job for the leading marketing research firm in the exploding computer services business, INPUT.  At that time, INPUT was the Gartner Group of its day.  All large and most small computer services firms subscribed to INPUT’s regular reports on the future of particular markets and technologies.  I was a Research Analyst, but soon became proficient in advanced forecasting spreadsheets and sifting through the media hype for indicators of future success.  In 1984, I authored an INPUT report on the commercial potential of “Artificial Intelligence,” which at the time was mostly experimental–except for those seeking venture funding.

Soon I began to “Awaken”, as Henri Bergson puts it, to my “spiritual immune system.”  My “journey is made by … choosing knowledge over the veils of ignorance.”  Like religion, beliefs are learned and earned in software technology through intense study.  Unfortunately, most public commentary about data normalization makes no sense, have no academic merit, and are vigorously defended by those graduate students trying to free themselves into the world of consulting.  Fabian Pascal nails these commentaries to his wall every week and responds with robust defensible academic criticism.  I thank him and C.J. Date  for saving me from wasting any part of my life learning stupid or regressive technologies that regularly come into and out of favor.  For instance, the “relational” vs. “object” debate finally came to reconciliation with “The Third Manifesto.”   But not many freelance web programmers know that.

I eventually left INPUT and started a freelance web programmer practice developing and selling application development and database development services for reinforcing steel fabricators.  That market was short-lived, so back to a job I went.  Having spent several years in database development for, among others, Travelers, GE Capital, and Hewlett-Packard, I came to the realization that by separating the business logic from the presentation logic I could improve code reuse.   So I created a system that uses database metadata to drive the presentation for any database table structure and dynamically creates user interface controls for displaying related data.    Soon thereafter, HTML became the preferred presentation layer descriptive language for application development, and Transaction Editor was born.

The owner’s of small brick and mortar businesses who use Transaction Editor for their unique database development remind me every month how glad they are to have met me.  For them, I solve the application development backlog permanently.  They don’t have to worry about losing the resource who wrote their application, finding another developer able to understand the language/framework/components and willing to study the business logic encapsulated in their application.  I am devoted to serving small businesses by giving them enterprise-class functionality at an affordable price.  My challenge to growth is to rise above the crowd of freelance web programmers and have a long enough conversation with potential clients to demonstrate my capabilities with the Transaction Editor service model.

I have used Guru.com for years and more recently added Freelancer.com.  I DON’T USE Upwork.com (formerly Elance.com and Odesk.com) because they place too many restrictions on freelancers, which are a dis-service to clients.  But browsing projects on these sites is a time consuming task mostly for their lacking search capabilities.  So in searching again for other channels to reach small businesses, I came across the latest entrant into the world of recruiter disintermediation, Toptal Software programmers network.  I’ve had a long career of recruiter-led on-site engagements.  But Total and recruiters in general, like their Human Resources counterparts, are focused on finding experience in particular technologies.  Again, failing to service clients with advised solutions.

The types of small businesses that Transaction Editor helps do not use recruiters.  They may ask their accountant or desktop support person for an freelance web programmer referral, but their accountants are not typically versed in application development enough to provide a recommendation.  I have had some success networking with desktop support and network engineering firms.  Younger small businesses seem comfortable searching the Internet, only to be inundated with questions about language and platform and framework, choices that they could not care less about.  Oftentimes I use Transaction Editor to replace a power-user home-grown spreadsheet or MS Access database application that has grown beyond their capabilities to support.  On occasion, they will have a 20-year-old custom application developed in some long-ago lost technology whose support has been sunset.

Because the people who need my services are unique businesses with no technical staff looking for a solution to a business process problem, they may not find me with a limited vocabulary of languages, platforms, and frameworks they do not understand.  The service I offer DOES eliminate the application backlog, is readily adopted by most brick and mortar businesses, and continues to provide me with multiple micro-streams of income.  Save them from the pain of re-writing their applications every 3-5 years by recommending me as their freelance web programmer.  They and I will be glad you did.

 

Employee Solutions To Organizational Bottlenecks

042414.MiniGs.Konduit

New Technology is hardly a solution without functional scope, user participation in the design process, and dedicated resources to analyze processes.  Internal process improvement starts with subject matter experts communicating the needs they have, and being able to translate that into screens and clicks that streamline, rather than complicate, the business process.

What is a database service?

Database as a Service (DBaaS) may sound like a service business, but really its just another specialized Infrastructure as a Service (IaaS). The CTO Vision article on DBaaS talks about hosting a database management system, but not hosting the application itself. Specifically, the author states “I believe that the reason for the draw is that databases, while critical to your applications, are generally a bit difficult to configure, scale and operate correctly. When presented with the opportunity to off-load that work to a provider, developers jump at the chance.” Developers seek help with databases because they were taught little in school about them and their experience becomes clouded by the many misperceptions about relational databases best described by Fabian Pascal. A good starter article is “Data Management’s Misconceptions” by Joab Jackson.

Software application development that relies on mash-ups of packaged products without professional services to clarify and organize the meaning of data is, well, meaning-LESS. Database management systems and programming languages cannot, by themselves, provide the meaning, integrity, and consistency that gives an application purpose and usefulness. Some ONE needs to be facilitating the discussion of what functionality is needed, and to clarify what the nouns and verbs used to describe a business process mean. Without this clarity of purpose, any software application development will eventually be doomed.

Konduit combines the database development and application development services needed to accelerate the iterative cycle of process adaptation to empower young businesses with a virtual “IT Department”. This service is delivered over an encrypted connection on the Internet, requires little upfront cost, no contracts, and a predictable recurring monthly fee structured to reflect the complexity of the application delivered.  But more importantly, this service includes all the database development, application development, workflow design, hosting services, data stewardship, and end-user support.

The term “database” is filled with mystery, but in reality it’s just about communication and logical organization, something every business needs in order to thrive. The more organized your business information is, the greater chance you will have to settle misunderstandings with your customers and vendors. If you can audit the history of data changes, you can identify and remedy mistakes and know who needs more training.  Database management systems provide a critical part of what is needed.  Information technology is more than hardware and software as commodities. Information technology REQUIRES PEOPLE who understand your business and can rapidly adapt your software-based processes to changes in the marketplace or operations.  To be effective, a freelance web programmer must understand how to use a database management system in the context of a business process automation application.

SQL Server on Linux 2017

Dennis Cheung writes about SQL Server on Linux coming in 2017.  To support an operating system and database system that runs on it requires a deep level of knowledge.  Troubleshooting issues, knowing the tools for diagnostics, and becoming familiar with the interactions and compatibilities among components is like the study of religion.  It requires a great deal of dedication and patience.  I started with Sybase (which birthed SQL Server) on Solaris (a Unix variant) many years ago and I transitioned to SQL Server on Windows.  I missed the Unix tools so much that I insisted on installing MKS Toolkit (Unix tools on Windows) wherever I went as a freelance web programmer.  I succeeded in building many data warehouses using these tools and welcome the addition of SQL Server to the Linux world.  Had Powershell emerged with “here” document capabilities back in 1995, things would be different.  Since Oracle now controls MySQL, and MariaDB is gaining momentum, I expect that the stability of the SQL Server database engine will gain some converts among the Linux community wishing for an economical vendor-supported product.  Despite popular belief, you don’t need .NET to run the SQL Server database engine, and you don’t need SSRS or SSIS to deliver reporting solutions and high speed streamed data integrations. The only tools MS needs to port with the database engine are the commandline versions of BCP.exe and SQLCMD.exe (or OSQL.exe or ISQL.exe), which are already available on the Sybase stack with a TDS driver.