Re: Object-oriented thinking in SQL context?

<dr.coffee1_at_gmail.com> wrote in message news:50df6983-ba3b-4604-994a-89b595775ea5_at_o20g2000vbh.googlegroups.com...
> On 8 Jun, 18:25, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
>> dr.coff..._at_gmail.com wrote:
>> > Hi folks.
>>
>> > I have a problem with wrapping my mind into the 'right' wrinkles.
> ...
>> > Any general ideas on how to design a SQL database around
>> > such constraints?
>>
>> > Dr. C.
>>
>> Those are mostly trivial data modelling problems. Have you read anything
>> on data modelling, normalization, joins?
>
> Yes, I have. Well, 'browsed' is a better term, as the
> objective is to get a working demo system up in a hurry.
> As age progresses, I'm more and more inclined to skip
> reading what is not immediately percieved as useful, so
> presumably I don't see the forest for the trees. Databases
> are the solution to the problem at hand; I just don't have
> the hands-on experience (yet) needed to come up with a
> working system.
>
> The problem is that I think in OO terms, like classes and
> inheritance. Decades ago I used to work very hard to get
> away from arrays and other non-OO data structures associated
> with procedural programming, and now I am unable to revert
> my mind to that context.
>
> In particular, I don't recognize OO terminology from what
> I read, and I am not able to recognize OO concepts from
> the terminology I do see. As somebody correctly pointed out,
> I am not used to the problem statement that needs to be used
> in DB design.
>
> So in the 'naive' problem statement I see an array of objects
> of classes derived from a base class (in C++ I'd use
> boost::shared_ptr to access the objects), while I read that
> SQL is constrained to 'trivial' arrays. The problem is the
> vast philosophical distance between the two problem statements,
> that I am unable to bridge.
>
> I'd appreciate any key words to look for when re-reading
> the material. I'm using Teorey's "Database Modeling and
> Design: Logical Design", 4th edition.
>
> Dr. C.
>
From your two uses of the word "trivial", I gather you are using it to mean some combination of easy and unimportant.

SQL tables are very easy for those who have learned to think in terms of them, to understand data requirements from a data-centric and set oriented point of view, and to implement tables that will be easy to keep current, easy to maintain data integrity, and easy to write queries against. The results you can acheive with SQL tables are far from unimportant. I read recently where somebody replaced sometihng like 20,000 lines of object oriented code with about 300 lines of SQL, and sped up the weekend update process from 45 hours to 45 minutes. The object oriented code in this case may have been clumsy. There are many clumsy designs where an OO language is used to merely code up a software design that is fundamentally procedural. When you do that, you get all the overhead of OO run time systems, without most of the power.

Something analogous happens with database design. People who begin designing databases based only on prior experience with indexed files, and who expect to put all the business logic in the application, end up with databases that are clumsy, slow, and fairly inflexible. People who know what they are doing can acheive outstanding results. Unless you are far more intelligent than I, mere "browsing" works on database design is not going to make you a competent designer.

Just as you moved away from thinking procedurally when you migrated to OO thinking, so likewise most of us who started with procedural languages and migrated to SQL moved away from procedural thinking. To you, thinking in terms of tables or arrays looks like a return to procedural programming. Believe it or not, coding up all the intelligence in a business system in Java looks to me like a return to procedural thinking. I would prefer to design a database that is relevant, flexible, fast, and powerful. I think I'm not alone in this perception.

One key difference between procedural thinking and SQL thinking is thinking in terms of sets of data rather than breaking a set down into a loop that processes elements one at a time. At some point, the SQL query optimizer may have to do that, in order to come up with a strategy for carrying out your query. But outthinking the optimizer is a singularly wasteful use of brainpower. It's much better to come up with logical data structures that are relevant to the problem at hand, and to implement physical data structures that capture the essential features of the logical data structures.

Back when I learned SQL, we tended to think of SQL as "relational". Since that time, in places like this newsgroup, I've learned about some flaws in SQL, when viewed as an implementation of the relational model of data. Those flaws are real. Nevertheless, SQL allows you to do a lot of the things that led Codd to propose using the relational model of data for large shared data banks. There is a learning curve here. You can shorten it, but you can't skip over it. Received on Tue Jun 09 2009 - 14:29:06 CEST