Designing for Multi-Platform Databases: A SME’s Perspective

When building products that interact with multiple database platforms, the complexity can be both a challenge and an opportunity.  For Subject Matter Experts (SMEs), observing design decisions made without sufficient knowledge of underlying database architecture can be particularly frustrating. These moments highlight the critical need for architectural foresight and platform-specific expertise to avoid pitfalls that compromise scalability, performance, and maintainability.

I’ve been managing multiple platforms for a couple decades and in recent years, building products that support the most popular enterprise and open-source databases today.  I’m keenly aware of lacking documentation, either due to limited resources or hopes of directing customer workloads to cloud solutions, education and expertise, so feel my developer and engineer’s pain.  On top of this, strict licensing and limited allowable use of the products leads many to make assumptions that simply aren’t correct, often building out a foundation that’s difficult to evolve, as it would require a complete rework of the product.  I often take it upon myself to make sure that what ends up in a customer’s hands is what I would want to use in my own environment with no excuses.

Doing things half-way just isn’t how I am wired and I often look back and recognize that I can be demanding of those around me.

The Multi-Platform Conundrum

Due to all this, developers tasked with creating products that span multiple database platforms often focus on abstraction and flexibility. While these goals are admirable, they can lead to design choices that overlook the nuances of each platform.

Common pitfalls include:

• Ignoring Platform-Specific Features: Over-reliance on generic SQL or ORM frameworks can prevent the use of optimizations unique to a specific database or native features that could streamline the development process.
• Inconsistent Data Types: Assuming uniformity across platforms can result in compatibility issues, especially when dealing with date/time formats, encoding, or precision requirements.
• Inefficient Query Structures: Queries optimized for one platform may perform poorly on another, leading to unpredictable performance, most often choosing vanilla SQL vs. PL/SQL built-in packages in Oracle, Windows functions for SQL Server or available extensions for PostgreSQL.
• Limited Scalability: A design that doesn’t account for platform-specific scaling strategies, especially cloud-smart ones, such as globally distributed database solutions and understanding architecture features and limitations, can hinder growth.
• Confusing User Interfaces: In a hope to be agnostic, the UI suffers, using incorrect terms or navigating the product in a way the user wouldn’t.  Unless the UI is redesigned, customers end up choosing the product they know over the single-pane of glass that enables more for less, which was the original goal.

The Cost of Overlooking Expertise

When foundational decisions are made without development expertise or fully understanding the intricacies of each platform, the consequences often manifest during scaling or production phases. These include:

• Unused Products: Assumed solutions for one platform might not work for another, resulting in UI and features that cost considerable time and money that go unused by customers.
• Inability to Implement New Features: The product can’t evolve if the baseline was designed in a limited way that only scoped an isolated architecture or without real-world scenarios.
• Escalating Maintenance Costs: Technical debt accumulates as teams patch issues that stem from poor initial designs and features.

For SMEs that are brought in for their expertise, attempt to address these issues after the fact, which is akin to reconstructing a building on a shaky foundation.  It may seem like a straightforward answer may exist for questions posed, but as we look at the underlying issues in the full solution, the only answer we are left with is no matter what, we end up going off a cliff or up against one.

While duct-tape solutions may exist, they are often costly in terms of both time and resources, while also being difficult to accept.

Proactive Solutions

To mitigate these challenges, organizations should prioritize collaboration between developers and SMEs from the earliest stages of product design.

Key strategies include:

• Platform-Agnostic Guidance with Platform-Specific Expertise: While abstraction is important, SMEs can help identify where leveraging platform-specific features will provide significant benefits vs. generic or incorrect ones based on the unknown.
• Unified Standards with Flexibility: Establishing data standards that account for cross-platform compatibility while allowing for platform-specific optimizations ensures consistency and performance.  Translators and a clear understanding of terms should be documented to assist teams.  SMEs should take a direct responsibility for this and ensure that they aren’t just dependent on vendor documentation for this.  In recent years, most detailed documentation, (in my opinion) has been impacted by the sheer number of technologies available and/or limited resources to provide documentation.  Although I never thought I’d advocate for having streamlined documentation for what is essential to support an organization, here I am doing so.
• Comprehensive Testing: Simulating workloads on all target platforms can reveal potential performance disparities or limitations before deployment.  Testing should engage the SMEs to verify that a test being performed is a valid use-case.  Testing should be done with real customers and an open discussion on how products and platforms are really used to help educate everyone involved.
• Regular Knowledge Sharing: Cross-training between developers and SMEs fosters mutual understanding and helps teams anticipate challenges unique to multi-platform environments.

Turning Frustration into Opportunity

While the frustrations of correcting misguided design choices are real, they also offer an opportunity to demonstrate the value of SME expertise. By guiding teams toward better practices, SMEs contribute to the creation of resilient, scalable products that fully leverage the strengths of each database platform.

In the end, building products for multiple database platforms requires a partnership between developers and SMEs. This collaboration ensures that both innovation and practicality are considered, resulting in systems that are not only functional but also adaptable to the ever-changing demands of technology. And for SMEs, knowing they’ve played a key role in steering projects toward success is a deeply rewarding experience.