Re: Object-oriented thinking in SQL context?

In message <47BXl.1634$u86.696_at_nwrddc01.gnilink.net>, Walter Mitty <wamitty_at_verizon.net> writes

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

I'm an enthusiastic Access user although I'm far from being an expert. But I know that it does have some serious weaknesses and in some situations actively encourages bad practise.

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

That's where a decision is required. Do you want to learn Access or do you want to learn relational theory. The two are related but they aren't the same thing.

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

That's one of its many disadvantages. It's another one of the ideas that visitors to this newsgroup occasionally espouse as the next Big Thing.

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

EAV systems generally require very high skill levels in all of the users. That's fine for a single-user database but generally won't work for more than one user, without an extraordinary infrastructure to maintain consistency.

-- 
Bernard Peek