I'm working on a web application that is publicly accessible. (Really) Long story short, we have two sets of tables in the database with relatively the same names and relatively the same data. The first set of tables are used by our web site, which I will call Set A. The second set of tables are used by an Oracle Forms application, which I will call Set B.

The reasons for duplicated tables:

  1. The data in Set B is the Holy Grail of information. Allowing the public to change this has legal repercussions.
  2. The public can partially fill out all of the forms in our web application, and in one final button click, finalize their information. They then have to pay a fee before downstream processing begins. Users may start, stop and restart this process any number of times.

My current task is to copy data from Set B into Set A in one operation, so that form fields are pre-populated. If we don't do this, every page in our web application has to pull data from Set A first, and if it is missing fall back to Set B (which sometimes involves data conversions).

I have this built and functioning correctly, but I'm not real keen on the names I've used: *Converter

I'm really trying to avoid a "service" object. Having read I Shall Call It.. SomethingManager at Coding Horror, naming anything with the word "service" makes me want to write a sibling blog post titled "I Shall Call It.. SomethingService."

My lead developer didn't like the "Converter" naming convention. I've searched for programming and design patterns for moving data between similar tables and I haven't found anything.

I've thought about "Migration" but this implies changing the database schema iteratively over a period of time (ala Active Record Database Migrations).

Whatever the name or pattern is, I'd like it to clearly communicate:

  1. Information is being pulled from one set of tables
  2. Mapped to a common object model
  3. And inserted into another schema

I also feel like I'm not the only person who has encountered this problem.

Is there a programming pattern, design pattern, or naming convention for moving data between related schemas?

Update: One of the biggest problems I have is that the web site tables (Set A) don't have primary or foreign keys! We have a bunch of meta data hidden in constants, whose values get mashed together sometimes into a single column.

@kevincline's answer to write a stored procedure is actually why I choose to handle this specific situation in code. We have a stored procedure to move data from Set A into Set B, and Oh. My. Goodness. The hoops the poor PL/SQL developer had to jump through to untangle our mess was nothing short of a miracle.

In this case, the code was much easier to write and maintain outside of the database because our web application ... well. Think of every database design best practice imaginable and then do exactly the opposite.

closed as primarily opinion-based by gnat, psr, Kilian Foth, jwenting, Bart van Ingen Schenau Oct 1 '14 at 13:29

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

  • @gnat: How can I improve the question then? – Greg Burghardt Sep 30 '14 at 18:18
  • did you check meta guidance referred in prior comment? – gnat Sep 30 '14 at 18:18
  • @kevincline: A stored procedure would not be easier to maintain. It's also harder to test stored procedures. The performance boost would even be negligible. The big advantage of doing this in code is that you can isolate this and unit test it without a database to back it. – Greg Burghardt Sep 30 '14 at 18:33
  • @Greg: I can't speak to your schema, and don't know your application language, but I've written a lot of PL/SQL to deal with horrible schema and found it much faster than writing and testing equivalent Java code. The key step was to factor out the JOINs into views. If the data volumes are large than it will be much faster to keep the data in the DB rather than incurring the overhead of de-serializing, instantiating objects to hold the data, and the re-serializing. – kevin cline Sep 30 '14 at 21:19

If you want to give a name to "moving data between related schemas": In Datawarehousing this is called ETL:

From Extract_transform_load@wikipedia

In computing, extract, transform, and load (ETL) refers to a process in database usage and especially in data warehousing that:

  • Extracts data from outside sources
  • Transforms it to fit operational needs, which can include quality levels
  • Loads it into the end target (database, more specifically, operational data store, data mart, or data warehouse)
  • On a more basic level, this is more like what I'm doing. It isn't just a straight copy, it is translating data from one format to another as well, plus a boat load of error handling. – Greg Burghardt Oct 1 '14 at 12:26

I would just write a stored procedure to do this instead of getting application code involved. It will be easier to develop and maintain and it will run faster. You may need only a single MERGE statement for each destination table.

  • Also giving this a +1 because this should be my solution, except for the error handling I have to do is much easier outside of the database. – Greg Burghardt Oct 1 '14 at 12:30

A design pattern would probably be more applicable how you accomplished the goal in source code rather than describing the goal itself. It sounds like this could have been coded up using Adaptor or Strategy pattern, but again, that would depend on the code itself.

It sounds like you're looking for a name to describe the whole process, then want to apply that to the code to describe that "this is the part of the code that converts/translates data from B to A". Depends a lot on the codebase. But you might consider naming it based on the actual names. SetBConverter might not be so bad, depending on context. Or OracleToWebDataTranslator or whatever... (now just getting into total speculation).

In general, a lot of computer science can be boiled down to data transformations. Moving between related schemas is a little more specific, but I can't think of any specific terms to cover that off-hand.

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