How to organize data flow in B2B solutions?

When you start any B2B business, you need to access partner’s remote data. Each company has its own database and you need to set up data import individually. The manual approach is like marching into the company headquarters and asking the CTO to show you where to get the data. Then repeat the process with every client. Sounds insane, right?

Scaling up your business isn't possible with this unbalanced workflow, and you need a universal way of accessing data across multiple databases.

In this article, we’ll learn how to do it, referencing a live example of a solution that connects a network of web stores that accept payments with a network of delivery companies.

Each store delegates parcel services to a number of delivery companies. Delivery agents need to access an internet store’s database to get the data needed for mailing, but they shouldn’t have full access to customers’ credit card information, etc.

Access a third-party database like your own backyard

A manual data access approach is feasible with up to ten clients. When your business grows and you connect with hundreds of companies, more and more people with different skills get involved and they need to access data across multiple databases. To satisfy everyone's needs, developing a convenient database backoffice will turn into a nightmare without proper tools.

Partner’s database is shrouded in fog for an employee from another organization, so you need to build a data access module that’ll help to read the DB schema. Let’s see what a data access module task is.

A database consists of many linked tables that contain all sorts of data. Sometimes, the required data is stored within XML or JSON fields. SQL is the key to translating a very complex structure into a readable table format. However, it’s merely a data access language, not a ready-to-use solution.

Data access module tasks:

• Reveal a database structure
• Let all types of users write SQL queries (even beginners)
• Instantly show result data as you type a query
• Map data to the structure
• Convert data to convenient file formats

The module should provide safe and controlled access to an external database through the easy to use and understandable interface. A little assistance from the partner company might be needed, but it’s nothing compared to making regular requests to provide papers and separate chunks of data.

Business example: Sending packages

Let's take a look at a specific example with a web store that sells various items, and several delivery companies that deliver the orders to customers’ addresses.

Let’s say that you own a B2B solution that connects to the web store and passes this data to a number of delivery companies. Both parties — the web store and the delivery company — benefit from this partnership. They don’t bother about the peculiarities of the SQL database but simply do their part of the job: grow their business. Both parties should also take care of confidential information, such as end users' personal data by providing limited access. This data must be safe according to the user policy and non-disclosure agreements.

When a user buys something from the web store, the order details should be sent to the delivery company ASAP, so the web store must grant direct, yet controlled access to its database.

The partner’s internet store has an SQL database of order IDs, a list of items in each order, customer names, delivery addresses, etc. Of course, all this data is represented in different tables that are linked with each other. One store may place multiple orders, and each order may include multiple items, so the information can’t be represented in a simple spreadsheet.

Imagine how long it would take for their DB admin to contact the web store, obtain the database schema, create the necessary SQL requests to set up a correct ETL (extract-transform-load) configuration for getting the necessary data and sending parcels. It’s ridiculous! No business can grow with such an inefficient solution.

Accessing the database implies getting only the required data (via SQL SELECT). A third party can’t write to the external web store’s database or read sensitive information (e.g., users' credit card details).

With this approach, you’re delegating the data configuration task to one of the parties, who knows what data should be provided. This party is also responsible for data protection and you free yourself from possible legal issues.

In the end, the user needs to do is match the data columns of this query to the fields in the destination table. This process is also called mapping. With simple drag’n’drop, the end user moves the data columns to the fields in the table that retrieves the data.

Voila! Now we have a ready-to-use query, with columns matching the columns of the recipient/receiving table. The only thing left to do is to import!

Summary

Data access is crucial for business processes between multiple companies. When you run a business, you don’t want to spend a lot of time digging around for data on a regular basis. Make Active Query Builder one of the core components of your solution for letting businesses interact and thrive together.

The component comes with a full set of tools needed for accessing databases, customizing the environment, executing SQL queries and performing other data-related tasks.

Check out the demo at GitHub and see for yourself how any PC user can be qualified enough to work with data tucked away in a maze of SQL databases.