How to manage individual inventories in Magento and access external databases (FMCG stores)

In today's world it is not only the ecommerce store that registers sales traffic data, but different systems are operated for the management of inventory, products, users and the company itself. These applications store the relevant data in databases, which have to be synchronized among the different applications. In case there are multiple applications, quick, immediate synchronization between them is exceptionally important in case of FMCG type online stores. In our article we are looking for solutions and making suggestions for such integrations.



What are we going to talk about in this article?

  • What is FMCG?
  • Some words about databases in general
  • Databases and Magento
  • Types of databases and their application for FMCG
  • Management of changes in inventory in Magento, questions and answers
  • Middleware: Middle layers, MOM systems

 

What is FMCG?

 

A short definition:

Fast-moving consumer goods or consumer packaged goods (CPG), in other words, consumer goods that move fast.

 

These are goods that are repeatedly purchased relatively frequently, e.g. with daily, weekly or monthly and the prices of which are relatively low. For example razors, paper tissues or babies products like wet wipes and, but bandage or band-aid could also be mentioned here.

 

Databases

A short definition:

A database is a sum of structured information uniform in quality that is managed by a database management application that is capable of storing, editing and requesting the data. The purpose of databases is the reliable long-term storage of the data and ensuring their relatively quick retrieval.

It is best to divide the databases into two groups, logical and physical databases. Logical databases are usually distinguished from physical databases based on their structure, operation and characteristics. They are represented by data models and physical databases can be accessed through multiple logical databases in case of modern databases, as if these latter ones created some kinds of covering layers around the stored data.

Data models and collections of Magento create a logical database exactly like that in front of the physical database in order to facilitate data processing and to make it more comprehensible. You can read in detail about data models and collections in one of our previous articles: Magento 2 module development – A comprehensive guide – Part 1

 

magento external database correlation between models

 

Databases and Magento

One of the most important parts of estores and all applications nowadays is the database, whose operation, maintenance and development usually costs a fortune. The databases may be distributed databases because the logical database of the Mangento online store “wraps” the connection and this way you can cut the costs and can manage the appropriate data at the appropriate locations as well.

Additionally, Magento is capable of managing several different databases simultaneously, for which it uses PDO, so you can easily build Cubrid, FreeTDS / Microsoft SQL Server / Sybase, Firebird, IBM DB2, IBM Informix Dynamic Server, MySQL 3.x/4.x/5.x, Oracle Call Interface, ODBC v3 (IBM DB2, unixODBC and win32 ODBC), PostgreSQL, SQLite 3 and SQLite 2, Microsoft SQL Server / SQL Azure, 4D-based connections in it.

 

Multiple database management

For your Magento online store, to be able to manage multiple databases simultaneously, it is enough to enter the parameters (host, username, password, dbname) of the accessibility of the new database in the config.xml configuration file of your module and to define the identification of the connection in connection > use.

Having done this, there is no software type obstacle in the connection, if the host is accessible through the network, the connection is established. The developers will only have to create the logical database (model, collection) after that for the smooth flow of data.

 

magento external database scalability

 

Databases: File-based sources

Experiences show that in most of the cases simple (csv) or complex (xml, json) source files are in the possession of the users. These files contain the complete product range, user data or even the orders. Magento also provides an opportunity to export these data quite easily from the administrative panel.

 

Advantages

  • They are easily manageable
  • They are easily modifiable
  • Relatively easily legible format for humans
  • Possibility of exportation is available, no development is necessary

 

Disadvantages

  • There is a high risk of errors when editing, which may result in the disintegration of the structure of the entire file
  • It is difficult to edit the records
  • Setting the code page of the files may cause data distortion
  • The total quantity of the data is processed during importing
  • It is not automatically updated

 

Database management systems

They are different, compared to the file-based sources, because you can reach your data through a database management system. The database management system eliminates the disadvantages due to the management of the files, because it offers easily applicable logical data models for that. In order to ensure physical data independence, file organizing systems, independent of the database management engine, have been elaborated, so that the independence of the organization of the storage and the device is ensured.

Based on data models there are relational, object oriented, network, deductive, object relational, deductive relational, deductive object oriented database management systems.

 

Storage organizations

  • optical
  • punched tape
  • magnetic tape
  • hard disk
  • memory database

 

Advantages

  • There is a user interface to the data
  • Query languages developed from general SQL
  • Functionality can be extended
  • Distributed systems are feasible

 

Disadvantages

  • They are complicated: logical and set management knowledge is necessary
  • Necessary to know the query language
  • Increased storage capacity is necessary for the database management and the data storage

 

Databases: Service-based sources

Database connections, where you have access to the database through an interface on another server that can be even on the other side of the world, belong under service-based sources. Even today you can have access to the data and manipulate them mainly through SOAP protocol or thanks to the developments through some kind of REST service.

The biggest disadvantage of the access through SOAP interface is that the interface is complicated and detailed, while the REST interface is a lot simpler and more comfortable. From a security point-of-view, the SOAP client used to be more reliable, however, thanks to the SSL connections and other cryptographic algorithms, the two interfaces are now equally reliable.

Development trends of mobile applications show that remote databases are accessed on a service basis, mainly as REST clients because due to the sizes of the databases their storage on the device would not be ideal in terms of neither hardware nor software performance.

 

Advantages

  • Performance: Lower performance is enough to run the application
  • Interface of the REST based services is simple (HTTP requests), it is easily comprehensible and has low resource requirements, the JSON based format is easy to handle, even on frontend level.
  • Documentation and error reporting of the SOAP protocol is more mature
  • General security can be purchased through SSL certificates

 

Disadvantages

  • Interface of the SOAP based services is complex, complicated, developments are expensive and its processing is difficult due to the XML format
  • It may be slow or even unavailable due to unreliability of network communication
  • Security is not 100% due to network communication, further cryptographic algorithms may be necessary

 

magento external database rest vs soap

 

 

Databases: External application sources

Databases of the corporate management systems do not significantly differ from the external databases, however they may be more complex, bigger (in size) than other databases built for a specific purpose.

Since these databases fulfil corporate management roles, they are continuously updated, changing and extended. For this very reason they have complex interfaces for the external systems, the integration of which may be a task of moderate to high complexity.

Fortunately most ERP, CRM and PIM systems have modules whose installation and minimal configuration ensure connection with your online store. It is very important that an immediate, two-way communication is created between these systems so that the least possible human resource is needed to be committed to administrative work.

 

Advantages

  • Communication may be completely automated, resulting in much less administrative tasks
  • It is enough to learn to use and to monitor a single system
  • Integration to the most well-known systems is quick and smooth
  • They have an advanced user interface
  • They can communicate through multiple protocols or interfaces

 

Disadvantages

  • Cross-section of the communication between the systems
  • Data loss in case there is no communication between the systems
  • Excessive human resources are necessary without two-way connection and/or asynchrony

 

Changes of products and changes in inventory

Immediate, but at least timely updating of changes of products and changes in inventory is a crucial factor in case of FMCG. This is important because if a new product arrives in the online store, it may not have to be immediately marketable in the estore, but if a product runs out of stock, the customer has to be notified at once about that immediately or the order has to be placed immediately with the supplier, the warehouse or the manufacturer.

This is a two-way synchronization in an ideal case, in other words both sides can inform the other that the data has changed and can automatically update the databases according to the business logic. This requires complicated development both on the online store’s and the database management system’s side even in case of two systems.

But there are cases when the task is to connect an ERP, a PIM and several ecommerce stores.

 

What can you do in such cases?

  • The first and maybe the most important question is how large quantities of data would you like to move?
  • Do you want to update the entire product range, products, orders and customer information?
  • Is it enough to periodically update the changes relevant in the given period, applying a queue solution?
  • All changes have to be immediately updated between the databases of the systems?

 

All the questions above are of key importance to be able to define what level of integration is necessary. In case of a shallow integration a daily csv/xml file based import from the external system to the online store, or even its automated running may be enough. The queue solution implies a moderate level of complexity, where one or both sides use a series of servers for the updates. In case of a deep integration synchronisation of the data can happen even in a middle layer (middleware).

We are listing below the differences between the various methods, shortly describing what each implies and how complex it is in the development list.

 

1.1 Replacement of the entire data

Replacement of the entire data is the so-called low-cost solution in relation to moving and updating data. Quantity of products and orders is an important aspect in this case, as such an update may take even minutes in case of several thousands of products, which may result in loss or product unavailability in the ecommerce store. For this very reason this solution is only suggested in cases where updating the products or inventory information either takes a relatively short time or the eventually prolonged updating can be well timed to the traffic of the online store.

 

1.2 Replacement of part of the data

Communication parts of data can be an option when product, stock or order information has to be updated several times during the operation of the online shop. Beside the timed solutions, where inaccuracy may cause problems, immediate synchronization may also be an option, as low quantity data communication is quickly realized in the system.

Stock management and integration solutions of the FMCG stores belong exactly here, as not the complete product, only the number of pieces is what changes.

 

2.1 Direction of data transmission: one-way, two-way

This is a relatively simple question, however, giving it a deeper thought raises additional questions. Furthermore, besides the increase of the integration costs, quantity of data communication may significantly increase as well.

One-way data communication is when there is a selected master system – for example an ERP – to which all other external systems, so for example your ecommerce store as well, sends the data. The main system (ERP) then processes and stores the data, and the administrators using the ERP manage the corporate processes based on the received data.

In case of two-way communication, both systems communicate with the other and they send the data through interfaces defined depending on or independent of each other. There is no subordinate or parent role any more here, only the priorities have to be appropriately defined or management of the changes have to be handled with a series of servers.

For further details about Magento ERP integration, you can take a look at our article: Magento ERP integration – how to plan it properly without making mistakes

 

3.1 Indirect transmission of data

Transmission of data can be done by indirect update (manually or in a scheduled way) or direct update (through API interfaces). There may be human intervention in case of the scheduled migration, a person who transfers the data from one system to the other.

This can happen in the form of a simple export-import process, where the data is saved in a file (export) and then this file is uploaded to another system through its interface made for that purpose (import). Of course this process can be automated if the data saved by the given application is uploaded automatically to the database of the other system at specified intervals (e.g. cron job).

It is quite clear that this method is slow and inaccurate even if the human factor is removed, and for this reason it is not effective from an FMCG point-of-view. However, experiences show that it may be sufficient in other cases.

 

3.2 Direct exchange of information

Indirect transmission of data is not exactly the best solution for the management of inventory data, since it is worth synchronizing the change in the number of pieces of the products as precisely as possible. Therefore, the obvious solution may be the direct exchange of information.

All kinds of different technologies are available, with the help of which you can realize this direct exchange of information, beside the built-in XmlConnect, API (SOAP or REST) solutions of Magento, you can buy ready-made modules for most of the external applications at Magento Connect.

It is undoubtedly one of the best solutions as synchronization of the data starts immediately, right when the changes occur, however, in case you have a special external application that does not yet have a connector (Magento module), integration costs will significantly increase. Do not forget that in the majority of the cases deep integration is necessary for the automated, immediate solutions, which, as described above, takes a lot of time and involves high development costs.

 

Indirect transmission of data is not the best solution for the management of inventory data. Click To Tweet

 

3.3 Automated or manual synchronization

It has been mentioned before that synchronization of the data between our applications can be done by the involvement of human resources and even automation of that process is feasible.

However, this periodical data communication makes synchronization of the inventory considerably more difficult. By automated synchronization we mean the scheduled task (e.g. cron job) here, when the process is launched by the system, but it could also be done by a person. These are the significantly cheaper solutions.

 

3.4 Asynchronous, immediate solutions

You can make inventory and data synchronization between your applications much smoother by applying asynchronous solutions. However, you cannot simplify the processes “to infinity” either, given that due to fixed dependencies of the business needs, asynchrony can result in efficiency only in case of certain tasks or parts of processes. And immediate data communication cannot be realized in 100% due to exactly the same dependencies.

It is one of the best, the cleanest and the most complicated solutions that involves significant development times and costs, so this is worthwhile choosing it only if it is really necessary for your ecommerce stores.

 

Intermediary layer: Middleware, MOM

The middle layer (middleware) as a solution for synchronization of stock and data communication is one of the fastest developing and modern technological solutions both in terms of hardware and software. We are talking about a solution (software and hardware) that realizes data communication based on the predefined business logic without external intervention, by coming in between our applications.

 

magento external database middleware

 

As a matter of fact, it is a semi-intelligent system through which the data flows and gets from one of our applications to the other depending on which part of which business process they are at.

It can be regarded as a gigantic labyrinth where your applications can be found at each of its exits and entrances, where the data reaches the gate of the other application through the labyrinth while following the rules displayed on the walls. The data is transformed into the appropriate format and takes on the data structure corresponding to the receiver side while passing through, so there is no need to transform the data in your applications.

These middleware solutions consist of separate servers with such complementary technologies like the Message Oriented Middleware (MOM), Redis memory-based cache and other software and hardware solutions supporting the middle layer.

Middle layers today are in the cloud, on memory-based servers, the API interfaces of which can be integrated with most of the online stores. Out-of-the-box solutions are also available nowadays on the market, with the help of which you can easily integrate your systems and synchronize your processes in the different systems.

These solutions are built on the general business processes and can more or less be customized, which means that they can be well integrated into online stores that do not satisfy special business needs. Bear in mind though that if your business processes are special and differ from the general business processes of the given application, the integration is also unique, it will work ideally only if it is customized.

 

magento external databases connection fmcg infographics

 

Conclusion

There is a great deal of solutions for the management of FMCG and by choosing the ones among these that are appropriate to you, you can build an optimal system that serves your Magento ecommerce store. Based on the above description you can define the criteria relevant for the decision and thus you can select the ideal solution.

However, you should not forget that without knowing the long-term goals and the given market segment, if you choose the simplest and (maybe not at all) the cheapest solution, it is quite possible that you will have to pay the integration costs several times.

If you are not sure about what decision you should make, write to us and we will help you choose the appropriate technology and solution thus saving considerable costs for your company.


magento_request
Do you need our support?
  • Magento Site Check
  • Magento Code Audit
  • Magento SEO Audit
  • Magento Project Rescue
Request help

NEED A RELIABLE, PROFESSIONAL MAGENTO DEVELOPMENT PARTNER?

Contact us if you have any question or requirement related to the preparation of a new or renewal of an existing online store.

Next