June 3, 2008 at 3:23 pm
Hi, I'm currently in the middle of an SOA implemenation at a major financial institution. For now we have approximately 4 systems which will be communicating with each other via messaging with more likely to come in the future. All of the messaging so far is being done on the database level. The type of investments we're dealing with do not have any kind of common identifier such as CUSIP, ticker, etc.
The challange we're facing is how can the systems communicate with each other about this common set of data? Each system has their own tables where they store the data and these tables all have identity columns. So one way or another we will need maintain mapping tables of all these identities. It gets complicated because some of the mappings are also one to many (ie one of the systems does not store as granular a level of detail as the others).
I imagine we can't be the only place dealing with issues like this? I'm curious about how other places handle this issue. Does each system have their own set of mapping tables? How do you maintain them? It was proposed that we create a centrlized identity mapping database which itself would be somehow automatically maintained via messaging (ie when a new piece of data is added in the source system it would let the mapping db know its identity which would then broadcast to the other systems, etc) To me this sounds good on paper but could be incredibly complex and difficult to maintain.
Does anyone have any insight to share on this topic?
Thanks!!
June 3, 2008 at 8:28 pm
It's not clear to me what the problem is that you are alluding to. Tables with ID fields as PK's refer to each other all the time without any problems.
Could you give some examples?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 4, 2008 at 6:43 am
We have similar problems. We've used two steps to resolve it. First, identify absolute owners of all the data you possibly can. The primary keys generated by those owning systems are the PK's that all the other systems have to use. No one generates their own ID's on that type of data. Second, any data that can be generated by more than one system, and we try to minimize that as much as possible, we built a key generation service that each of the systems has to subscribe to and the keys are generated at a central point.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 4, 2008 at 7:22 am
The issue is that these systems are not all in the same database or server. The systems communicate with each other via messaging. e.g. The trade entry system sends out a message saying a new trade has been entered. One of the pieces of information about the trade is the ID of the security. The types of securities we're dealing with don't have any kind of global identifiers that everyone knows, like tickers. So we send the internal primary key ID in the message. The receiving system has to know what ID that corresponds to in it's own tables in order to process the trade.
Grant, thanks for the info. Your solution of the central mapping system is one option we're considering. I'd like to avoid that if possible though becuase it seems incredibly complex to manage that. Also our situation is different becuase most of the mappings are one to many. This means there will most likely have to be some kind of human intervention in the process because someone has to decide which items in one system "roll up" to an item in the other system.
Supposedly this is an "SOA" architecture. I thought that meant that each system should be unaware of how other systems handle data. I always wonder how this could be possible given the situations that we're having? Maybe this isn't a "real" SOA architecture?
June 4, 2008 at 7:39 am
It sounds like you're not exactly working from a good set of SOA standards. By and large, in most cases, you should have one system defined as the absolute authority on certain data types and the other systems then don't have to worry about generating those keys. They simply consume them.
We do have a situation where we generate keys internally in a system for data that feeds from another service, but those keys are internal, they don't require remapping back out to the other system or any other system.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 4, 2008 at 12:34 pm
Seems like "What Security is mapped by this ID?" should be a Service also. At least in an SOA environment.
In a non-SOA environment, I would have a Securities table with a GUID primary key replicated to all of the DB's allowing multiple writers.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply