Should I use messaging instead of database
I am developing a system that will allow users to take data from one system and send it to other systems. One of the destination systems has a complex SOA (web services) and the other has a mainframe that accepts flat files for input.
I have created a database that has a PublishEvent table and a PublishEventType table. There are also normalized tables that are specific to the type of event being published.
I also have an "interface" table which is a simplified version of the normalized data tables. The end user has a process that puts data into the interface table. I'm not sure about the exact process - I think this is some kind of reporting application that allows you to export the results to a SQL table. I then use SSIS package to pull the data out of the interfaces table and put it in a normalized data structure and create new rows in the PublishEvent table. I use a flat table because when I first showed them the relational tables, they seemed to be very confused.
I have a windows service that monitors for new rows in the PublishEvent table. Windows Service extended with plugins (using MEF). Which plug-in is called depends on the value of the PublishEventTypeID field in the PublishEvent string.
PublishEventTypeID 1 invokes a plug-in that reads data from one set of tables and invokes the SOA web service. PublishEventTypeID 2 calls a plug-in that reads data from another set of tables and creates a flat file that will be sent to the mainframe.
It seems to me that I am implementing the "Database as IPC" anti-pattern. Should I change my design to use a message-based system? Is the process of moving data to a flat table and then to normalized tables redundant?
EDIT: it's being developed in .NET 3.5
source to share
A MOM is probably the best solution, but you should also consider the following points:
- Do you already have a message-based system as part of your customer architecture? If not, it might be overkill.
- Do you have any experience with message-based systems? As Jason Planck correctly stated, you must consider specific patterns for them, for example, to ensure the chronological order of the message, management of dead letter channels, etc. (see this book for more).
- You mentioned the mainframe system, which seems to have limited options for interaction. Who will care about the layer that converts the "messages" (either DB-based or IOM-based) into something the mainframe can digest? Assuming it's you, is it easier (for you) to do this by referring to the DB (perhaps you have already worked on the problem in the past), or will it be different depending on your DB or MOM usage?
To summarize: if you are more confident that after going down the DB path, it might be better to do it, even if, as you correctly guessed, it is a bit "anti-pattern".
source to share
Some key elements to consider are:
-
String sequence consistency - does your data model depend on the order of the generated data? If so, does your schema ensure that the pub and support activity is generated in the same order in the original data?
-
Do you have ID columns on both sides? They pose a problem as their meaning continues to change depending on the order in which data is entered. If the Identity column is the only primary key (surrogate key), changing its value may render the data unusable.
-
How do you prove you haven't lost the tape? This is the hardest part of the solution, especially if you have millions of lines.
In terms of architecture, you can check the XMPP protocol - Smack for the client (if Java) and eJabberD for the server.
source to share