I am at kind of a loss on how to design a certain database project I am
working on. Basically, we have a proprietary program with a standard
backend (though we do not have direct write access to the backend).
What I am working on is a project to take certain data from the
database, send it to certain vendors, process the data, and send it back
to the proprietary program.
Here is the basic flow as is supposed to happen:
1. Extract data from the program based on a single criteria.
2. Format the data.
3. Send data to Vendor1.
4. Get back data from Vendor1.
5. Based on the responses from Vendor1, take any results that Vendor1
could not handle and send to Vendor2.
6. Get back data from Vendor2.
7. Combine the results from Vendor1 and Vendor2 as appropriate, and
then import back into the program.
The vendors perform validation of our certain parts of our data. Only a
subset of the data sent to Vendor1 is sent to Vendor2 as we are assuming
that Vendor1 has the best data.
When we get back results, there are a few possibilities:
1. They can tell us that they have high confidence that the data we
submitted is either correct or that the data the provided is better.
2. They can tell us that they have low confidence that the data we
submitted is either correct or that the data the provided is better.
3. They can tell us that they have can't help us with that particular
submission (submission being defined as a row of data; more than one row
is typically submitted in one batch).
4. They can tell us our submission was not formatted properly.
5. They can tell us that their data processing servers hiccuped and
that we should feel free to try again.
One of the tricky issues is that submissions to Vendor1 and Vendor2 use
different specs. While currently we are sticking with these two defined
vendors and their specs I have to allow for the possibilities that:
1. Additional vendors could be added to the process.
2. Those additional vendors could be put in anywhere within the process.
3. The specs could change for submission to those vendors.
Additionally, I need to keep track of how the information is processed
so that I can go back into the code of our separate processing tool and
figure out how to fine-tune the processing. Also, since we get charged
by the vendors for their services based on what they send back I need to
keep track of:
1. What submissions were done.
2. When they were submitted.
3. What came back.
4. How confident they were in their data.
5. How many times a row was submitted.
6. The data flow from when the data is pulled
At some point more than one person may be submitting data, but at this
point I am the only one who deals with this particular process.
I figure there are basically two ways of dealing with this:
1. Create one table for each vendor, with each table designed for the
specs of the submissions.
2. Create one table for all submissions, with the table having
redundant rows because the submissions defer depending on which vendor
is used.
In trying to go through google I was hampered by the fact that I do now
know the technical term for such a database setup. If someone could
just tell me what this kind of setup is called, I should be able to find
the resources on my own. Failing that, if someone knows of an archive
of different database designs for different industries I would be most
appreciative.
Marc DVer
