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?

2 thoughts on “Discrete Data and Discretion”

  1. So, I’m intrigued but don’t get it. It sounds like you want someone to tell you not to do something you already don’t want to do.

    You’ve not made the reasons for why you want the abstraction clear enough for me to really comment.

    Is it the case that the UI has a static virtual schema or view on the entire dataset? That is, does the UI only care about Name, Firstname, Lastname and Size? Does any system care about the “other data”?

  2. Yeah, I can’t understand the question either.

    I bet you probably couldn’t either, take a look at your mild nightmare of an actual question(?):

    “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”.

    Can you do another post on this and break it down in a little more detailed way? Or email? Chris’s questions would be a good start.

Comments are now closed.