Back to the Data Model
The Area being Modelled is :-
Importing Client Data
I've spent weeks even months trying to learn DB design to aid my business process.
Certainly a novice, don't pretend to know or fully comprehend best practice at all.
No matter how far I get the same problem keeps stalling me:
how to design/structure DB that is a master data repository for all my business data and
also serves as a "staging" point for disparate data (fields, naming conventions, etc)
that is imported daily from multiple clients and needs to conform to a single convention
to be used in business process.
I am using ms access.
I took all fields from all various imports and attempted to normalize them by separating
into unique categories and setting up relationships.. which could easily be a mess already.
the daily imports come in .xls or .csv form and present the following problems to integrate:
1. the import data is not normalized, and would have to be updated across multiple tables.
it is a mind bender trying to do this, access does not seem to allow update/append to from one source to multiple tables
2. resolving naming conventions/etc... on import I need to perform comparisons of data to
transform it into my "conformed" structure.
I tried setting up many-many/lookup tables with all known possible data from client and it's
"conformed" equivalant so that the transformation could be automated on import...
but the mechanism to accomplish this completely eludes me.
I've probably already said enough to lose anyone's interest in trying to help :) so I'll leave it at that.
I am a "do it myselfer" with a decent amount of IT competence but no expert at this.
It is worth it to learn b/c only I can design exactly what will serve my purpose.
I've tried hiring consults and they want me to defer to their idea of what I need which is an
unacceptible compromise in my book.
Anyone interested in guidance/assistance?
I don't mind to pay but I'm not looking for someone to do the work for me, I am looking for a mentor/tutor.
B. Barry says the Things of Interest are :-
3. Data Staging
4. Others to be determined
C. The Business Rules are as follows :-
1. To be determined.
© DataBase Answers Ltd. 2011