Re: Object-oriented thinking in SQL context?

<dr.coffee1_at_gmail.com> wrote in message news:35fe9916-b423-49e4-95f4-b42bf672a4c7_at_x5g2000yqk.googlegroups.com...

>That's what confused me. The general concepts are *similar*
>to concepts from OO, but not the same. I suppose the main
>difference is that objects are local collections of different
>attributes, while in relational databases the objects are
>'disassembled' and the attributes distributed over the
>various tables.

If the mapping between OO concepts and RM concepts were simple, someone would have, by now, written a book that ties the two of them together, and explains both patterns in terms that makes sense. to someone coming from one pattern or the other. To my knowledge, there is no such book. I'm going to echo something Bernard said in another response. Better minds than mine have attempted to reconcile the OO way of thinking and the RM way of thinking with each other. By their own reckoning, their attempts have been unsuccessful.

So you really shouldn't expect to reduce RM to a pattern that you already know. And you shouldn't expect, based on your success with OO up to this point, to be able to treat RM as something that can be marginalized as either a specialized niche or a trivial development. It is neither. Possibly the best person to speak to this would be Marshall. Marshall is a regular here who spend at least ten years as an OO programmer before going over to the RM side. He might be able to relate to what you do and do not understand at your present stage.

Meanwhile, I need to correct the general thrust of my first response to you. In that response, I contrasted the OO way of thinking with the SQL way of thinking. That completely overlooks the fact that your target is to build an Access application that stores and serves up data for analysis with Excel. Much of what I said, while true, is beside the point. You can, in fact, build a simple yet functional MS Access application without learning much of any relational theory and without gaining much of any proficiency in SQL. Sadly, I can't help you do that, because I learned SQL before my first exposure to Access. So everything new that I learned in Access was something that I related back to what I already knew in SQL. After playing around with some wizard for a while, I would switch to the "SQL view" in order to "Find out what's really going on".

That was a useful way for me to understand it, but may be the long way around for you.

Like you, I like to learn the big picture first, and then fill in all the details. I was fortunate to learn the big picture of relational (or at least SQL) databases back in the 1980s, when there was less to learn. I find MS Access tutorials to be infuriating, because they all approach the subject matter as if there is no big picture. Here's how you chage the font in a dialogue box. And here's how you declare a primary key. And here's how you use the forms wizard to create a form. Gee whiz! Isn't that wonderful! But there's no central unifying theme in the narrative.

Anyway, it turns out you can do almost everything in design mode that you might be able to do by learning the syntax and semantics of SQL DDL and DML. And all you have to do is point and click, most of the time. And, because MS Access integrated application building with database building, you end up building a functioning application alongside the building of a working database. That's no small benefit. I actually built some useful stuff for myself using MS Access without really learning it. I hope somebody who really has learned it can help you more than I can. In the meantime, I'm going to focus on some sidelights.

MS Access owes a lot to its heritage. It was built to make desktop database work available to the same kind of person who was comfortable with Excel or Word. Desktop database work is very different from enterprise integration database work. Access was typically for one user at a time. It wasn't easy to get data into an access database, except by interactive data entry. If you were dealing with more than 10 million rows, you were probably maxed out. And so on. Over the years, it has matured to where it has overcome a lot of the initial limitations. But it still looks like a strange beast, to those of us who grew up with industrial strength DBMS products. It would be sort of like Object Oriented COBOL, if you get my drift.

If you learn MS Access from the ground up, you are going to learn several things about the RM that just ain't so. That is why some of the regulars are chiding me for encouraging you. The design patterns that work for you are design antipatterns at the larger scale. That may not matter to you, at least for a while.

There is one antipattern that I want to mention in particular. Your problem description, in the original post, reminds me of the Entity-Atrribute-Value (EAV) antipattern. Different insrument types are like different entities, different calibrations are like different attributes, and different measurements are like different values. The terible thing about EAV is the hell you have to go through to create meaningful queries out of that mess, if you're using SQL or any language remotely like it.

But there is a tool that can reduce an EAV data glob to a legible summary without much difficulty. It's the Pivot Table Report in MS Excel. A pivot table doesn't care whether the row headers and column headers are data or metadata. It's all databurger to the pivot table. So, if your plan is to simply pump data into Excel, and reduce it there, you may be able to get away with an EAV like design, in spite of the fact that people like me consider it a monstrosity. Received on Wed Jun 10 2009 - 00:12:16 CEST