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.
6
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)
1
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.
1
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.