Attention, geeks:
I have an interesting database problem that I must grok to determine a course of action.
I have set N, a set of n datasets. The datasets are consistently formed flat-file tables. For each of the sources of members of N, there is a distinct schema.
For reasons of data purity and to minimize the differentation between the source set and the working set, I have chosen not to normalize the tables when I bring them into a workspace and add a presentation layer. Currently, the presentation layer is recoded for each dataset’s schema.
The flat-file tables are saved as text files and imported into Access to an Access table which has at least the same structure as the flat-file, but which also includes additional columns. For purposes of this musing, consider this a generic SQL question.
The presentation layer – the UI – incorporates some minor aggregation and analysis which is enacted when the record is displayed, but not stored.
What I’d like to understand is if it’s possible or advisable to add a layer of abstraction under the UI which would permit me to display records from each or all of the members of the datasets without having to either work out the full normalization for all the tables or having to unify the tables’ schemas to accomplish a simple normalization. The reason I;d prefer to avoid doing this is to simplify errorchecking in the case of observed discrepancies between the working data and the source data.
For example
table MYPETS and table YOURPETS contain similar data in differing schema.
MYPETS:
Name Nickname Size Weight Breed Species
Chloe Chloe-bo Small 6 lb Mixed Housecat
Simon Sweetypie Medium 8lb Mixed Housecat
YOURPETS
Nickname FirstName LastName Kilos Diet PeltColor Size
Poo-poo Sherlock Smith 6 Vegetarian “White with Spots” Medium
Linus Linus Smith .2 Birdseed “Green feathers” Small
I can discard the unique-to table data in the UI, so what I’d keep is (Name, Firstname, Lastname, Size) for display.
What to do?