Back story: I work for a startup which was acquired by a large Fortune 500 company. I'm the lead engineer on a project which aims to consolidate duplicate data stored by both companies. Our new corporate overlords have a mature RESTful API (written in Java) exposing all their data which happens to live in a Oracle DB under the hood. Our startup codebase has similar data living in a MS SQL DB with no real nice API and a hand-rolled access layer written in C#/F#.

Part of my job is to come up with a mapping between these two data stores so a translation layer can be written. I'm happy to work at any level of abstraction and the most obvious one to me is translating POJOs <> C# entity objects.

My problem is that this mapping gets quite convoluted, and I need product managers with much more domain expertise than me to sanity-check what I'm doing. That's great, except the only way of expressing data they understand is SQL, to the point where they'll rewrite my strawman proposals in some kind of pseudo sql format they feel more comfortable with.

Given the differences in data stores & tech stack and limited size of TPM's comfort zone, is there a better way of expressing mappings between a bunch of business objects that doesn't have a huge learning curve?

  • Are you saying that you are giving them code as your mapping description? – JimmyJames Jul 20 '18 at 15:42
  • No, I'm using a combination of omnigraffle & confluence with boxes & arrows everywhere and looking for a clearer alternative – Chris Mowforth Jul 20 '18 at 15:56
  • 1
    It strikes me that the reason they're resorting to SQL is because they're familiar with it and don't understand your diagrams. Is it possible to give an example of the sort of thing you're showing them? – Liath Jul 20 '18 at 16:02
  • that's exactly it- I can't easily give an example without spending a while scrubbing proprietary info but suffice to say they're informal box & arrow style things. Basically I'm using the wrong tools. I just discovered my org has a lucidchard subscription so I'm going to run with that – Chris Mowforth Jul 20 '18 at 16:05

ERD diagrams seem like a pretty good way to accomplish this (especially if you ignore the links parts). Just looking at the names of the tables and the data in the fields corresponds pretty naturally to any other representation. The only thing that's hard to capture in a very sensible modeling neutral way is the hierarchy/relationship, but in this case, thats probably pretty easy too, because both existing systems already have been mapped down to SQL (so easy to generate ERD diagram for each).

| improve this answer | |
  • thanks, not sure why I didn't think of this :/ They're in a little world where everything lives in a MS-SQL table they have control over and we're opening them up to a world of many heterogeneous data stores, some of which aren't even relational databases... – Chris Mowforth Jul 20 '18 at 15:59
  • Understood. This is a little outside my area of expertise, so I was kind of afraid to mention it ;-). Glad I was able to help ;-) – Lewis Pringle Jul 20 '18 at 16:08
  • I can see how you might use this to model the structure of your database but I'm not clear on where the mapping goes. – JimmyJames Jul 20 '18 at 16:21
  • It depends a little on how close the models are. But the gist of these diagrams is a picture of of an object (e.g. User, or Address, or Factory) and a list of attributes. If the two are close, you show the Object name, and highlight differences (like both have table User, but must add 'birthplace' from one). If they are far apart, you are in deep doo-doo, but can abstract and just say "this object roughly corresponds to that object, and deal with differences on a drilldown basis). It's not perfect, and there maybe better tools. But this is a viable sensible approach. – Lewis Pringle Jul 20 '18 at 16:34
  • So the key is that ERD pictures make a great STARTING point for describing differences. Anywhere you need to drill down, you take one picture and put it next to the other picture and highlight the differences (and how to resolve them). – Lewis Pringle Jul 20 '18 at 16:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.