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:
- The data in Set B is the Holy Grail of information. Allowing the public to change this has legal repercussions.
- 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:
- Information is being pulled from one set of tables
- Mapped to a common object model
- 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.