mike.whybark.com

Per request, here is a followup to a previous post.

To reiterate, I’m thinking through the best way to present a uniform UI atop varying data tables. I am reluctant to invest the time and effort into developing import routines for the data in order to apply full normalization to the data tables for reasons which are sufficient and not under discussion.

Therefore the choices are:

Can I present data in an Access UI where the display fields draw from more than one data datatable, using if-thens and string concatenation to present the data as appropriate to the record?

or

Must I create a supertable which unifies the schema without any attempt at normalization in order to do the same thing for each record?

The answer is partially embedded in Access’ limitations on tying tables to the UI, it looks like. When I experimented with relating two tables to one “Form” (Access’ term for the UI) Access required the tables to contain a join; the data is not inherently joinable. Therefore if I want to do if-thens and concatenation to conduct normalization as each record is displayed (which is conceptually what I’m talking about) I will need to create supertables. Yuck.

One of the issues with doing this is Access is that Access does not permit dynamic column population at import. Therefore I can’t test the inbound data for specific characteristics to record the data source in the new record. That’s part of the MS upgrade-path strategy which is so irritating – leaving out simple-to-implement features as the consumer-grade app is upgraded forces advanced users to look at MS-SQL, in this instance.

So what it looks like I will be doing is recoding Access applications for each datasource and requiring the user to switch between them as they work with the data, which kinda sucks. Oh well.

Lately I have been working my way around the sprawled furl of moss and crabgrass that is our lawn, where it intersects with and overlaps the mysteriously vast expanses of slurred and broken concrete aprons and purposeless cement curbs that measure our property’s internal geometry. The tools I use as I ruthlessly demarcate, again, the boundary between the organic and the architectural are a square-end spade and a half-moon edger, the geometer’s straight edge and half circle, Apollo’s rule and Diana’s curve.

Reclaiming the formerly fringed and fronded concrete is hard work, and leaves me sore and winded each evening before I clamber up the steps to the kitchen to cook dinner. It’s the only exercise I have been getting since moving out past the end of the sidewalk.

The mats of mossy grass and grassy moss I dismember are generous in scope, in some cases nearly a foot wide and a couple of yards long. Some of the trimmed flash is nearly pure moss, and has the soft, light texture of human hair. Loth to simply chuck the trimmed turf, I have been laying it atop areas of the lawn previously denuded due to shade and root competition, expecting to prune the overhangs shortly.

As I step, hard, onto the lip of the edger, feeling the satisfying ‘chunk’ of the blade as it scrapes along the edges of this concrete coffer or that cracked pavement, I muse and curse. I don’t believe the work I’m performing is moral. I believe it’s an expression of the spirit of evil in the world, of territoriality and division and inorganic order over biological diversity and lushness. I’m the executioner. I’m the enforcer. I’m wasting my time and at the same time committing sins against my nature and that of the world.

I’m probably going to need some pretty decent pruning shears when I get to the bushes and the trees.

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?