Database Model - Class, objects and interaction

Hello,

I'm relative new to OOP and I'm curious about what is the best way to
model and implement (OOPL) a class that will be used to take care of
database connection, queries and results.

The idea is to have a single connection to a database or a pool of
them, but inside a single object (right?) is a singleton the best
design patter to follow? or is there a special patter for this?

Several objects will need at some point to store or retrieve
information from the database, how should this interaction be done?
just calling a method (of the database object) to store/retrieve
information from it?

Thanks & BR!

Responding to Paco...

> I'm relative new to OOP and I'm curious about what is the best way to
> model and implement (OOPL) a class that will be used to take care of
> database connection, queries and results.

The first step is to identify the problem space and the abstractions
that are appropriate for that problem space. The problem space is
actually a hybrid composed of: Codd's Relational Data Model, whose
abstractions are things like Table, Tuple, and Field; and the practical
DBMS implementations, whose abstractions include things like Schema,
Query, and Transaction.

You then encapsulate those abstractions as a cohesive subsystem behind
an interface that is tailored to the data needs of your application. If
you capture the invariants of the persistence paradigms properly, you
will be able to reuse that subsystem across applications by simply
providing different interfaces and external configuration data for
particular database/application contexts.

As it happens, the subsystem interface will almost always be a class in
the form of a GoF Facade pattern.

[You might find the categories "Application Partitioning" and
"Invariants and Parametric Polymorphism" on my blog to be of interest.]

>
> The idea is to have a single connection to a database or a pool of
> them, but inside a single object (right?) is a singleton the best
> design patter to follow? or is there a special patter for this?

A single object is not going to cut it unless your data needs are
trivial. The subsystem interface provides a single connection between
your application's problem solution and database access. That connection
also decouples the implementations so that the problem solution only
needs to think about the data that it needs in the specific problem context.

IOW, your problem solution doesn't care if the data is stored in an RDB,
OODB, flat files, or clay tablets. The problem solution just says,
"Here's a pile of data I call 'X'. Store it so that I can get it back
later by asking for 'X'".

Managing multiple databases is really an issue for the DB access
subsystem (e.g., you add a Database abstraction with relationships to
the other entities like Schema and Table). There must be some unique
mapping of identity of data in the problem space and data stored in the
database(s). That mapping would include which database is relevant. Thus
the "X" in the example needs to be uniquely mappable to particular
databases by the DB Access subsystem.



--
There is nothing wrong with me that could
not be cured by a capful of Drano.

H. S. Lahman
hsl@pathfindermda.com
Pathfinder Solutions
http://www.pathfindermda.com
blog: http://pathfinderpeople.blogs.com/hslahman
"Model-Based Translation: The Next Step in Agile Development". Email
info@pathfindermda.com for your copy.
Pathfinder is hiring:
http://www.pathfindermda.com/about_us/careers_pos3.php.
(888)OOA-PATH

On Nov 7, 7:58 am, "H. S. Lahman" <h...@pathfindermda.com> wrote:
> Responding to Paco...
>
> > I'm relative new to OOP and I'm curious about what is the best way to
> > model and implement (OOPL) a class that will be used to take care of
> > database connection, queries and results.
>
> The first step is to identify the problem space and the abstractions
> that are appropriate for that problem space. The problem space is
> actually a hybrid composed of: Codd's Relational Data Model, whose
> abstractions are things like Table, Tuple, and Field; and the practical
> DBMS implementations, whose abstractions include things like Schema,
> Query, and Transaction.
>
> You then encapsulate those abstractions as a cohesive subsystem behind
> an interface that is tailored to the data needs of your application. If
> you capture the invariants of the persistence paradigms properly,

This is misleading. Databases are about much *more* than just
"persistence". Sure, you can use them for only persistence, but that
would be a waste of tools in most cases.

> you
> will be able to reuse that subsystem across applications by simply
> providing different interfaces and external configuration data for
> particular database/application contexts.
>
> As it happens, the subsystem interface will almost always be a class in
> the form of a GoF Facade pattern.
>
> [You might find the categories "Application Partitioning" and
> "Invariants and Parametric Polymorphism" on my blog to be of interest.]
>
>
>
> > The idea is to have a single connection to a database or a pool of
> > them, but inside a single object (right?) is a singleton the best
> > design patter to follow? or is there a special patter for this?
>
> A single object is not going to cut it unless your data needs are
> trivial. The subsystem interface provides a single connection between
> your application's problem solution and database access. That connection
> also decouples the implementations so that the problem solution only
> needs to think about the data that it needs in the specific problem context.
>
> IOW, your problem solution doesn't care if the data is stored in an RDB,
> OODB, flat files, or clay tablets. The problem solution just says,
> "Here's a pile of data I call 'X'. Store it so that I can get it back
> later by asking for 'X'".

Again, this only applies if you use the database merely as a dumb and
simple filing system. The potential power of a RDBMS is far beyond
that of a filing system or a clay tablet. True, some OO fanatics do
only use the DB for such, and happily re-invent bunches of database-
like idioms in their app code, but that is poor practice in most
cases. Paco, don't let fanatics and blowhard consultants talk you into
using the wrong tool for the job.

>
> Managing multiple databases is really an issue for the DB access
> subsystem (e.g., you add a Database abstraction with relationships to
> the other entities like Schema and Table). There must be some unique
> mapping of identity of data in the problem space and data stored in the
> database(s). That mapping would include which database is relevant. Thus
> the "X" in the example needs to be uniquely mappable to particular
> databases by the DB Access subsystem.
>
> --
> There is nothing wrong with me that could
> not be cured by a capful of Drano.
>
> H. S. Lahman

-T-

Responding to Jacobs...

This response is directed primarily at the OP. So don't get your hopes
up that I will participate in one of you chain-pulling exercises.

>> You then encapsulate those abstractions as a cohesive subsystem behind
>> an interface that is tailored to the data needs of your application. If
>> you capture the invariants of the persistence paradigms properly,
>
> This is misleading. Databases are about much *more* than just
> "persistence". Sure, you can use them for only persistence, but that
> would be a waste of tools in most cases.

Separation and isolation of concerns is fundamental to managing
complexity at any level and in any environment. In a computing
environment it is a fundamental mistake to merge problem solving and
data storage because they are quite different subject matters with quite
different invariants.

That can work for niche processing like CRUD/USER where you live because
the form-based presentation paradigms have been deliberately defined to
map into RDB structures easily. IOW, the computing can be abstracted
into a specific RAD IDE paradigm for the sorts of DB/UI pipeline
applications where the major problem being solved is data entry and
presentation. That is, the user does the data analysis and reacts to it
once it is in a convenient format, not the application.

But for applications that solve complex business problems that involve
actually analyzing and processing the data it just leads to
unmaintainable applications. In those cases the concerns need to be
separated. You can use DBMS-based tools for things like reporting on
inventory activity, but if you try use such tools to manage the
inventory it would be a maintainability disaster in the long term. Not
to mention that the table-level query/join philosophy would be
horrendously inefficient for the solution since the same data is likely
to be accessed repeatedly during algorithmic processing.



--
There is nothing wrong with me that could
not be cured by a capful of Drano.

H. S. Lahman
hsl@pathfindermda.com
Pathfinder Solutions
http://www.pathfindermda.com
blog: http://pathfinderpeople.blogs.com/hslahman
"Model-Based Translation: The Next Step in Agile Development". Email
info@pathfindermda.com for your copy.
Pathfinder is hiring:
http://www.pathfindermda.com/about_us/careers_pos3.php.
(888)OOA-PATH

> IOW, your problem solution doesn't care if the data is stored in an RDB,
> OODB, flat files, or clay tablets. The problem solution just says,
> "Here's a pile of data I call 'X'. Store it so that I can get it back
> later by asking for 'X'".

In this case, you would only need one table in your database

create table theonlytablethatiseverneeded
(
id integer,
data blob
)

/frebe

> >> You then encapsulate those abstractions as a cohesive subsystem behind
> >> an interface that is tailored to the data needs of your application. If
> >> you capture the invariants of the persistence paradigms properly,
>
> > This is misleading. Databases are about much *more* than just
> > "persistence". Sure, you can use them for only persistence, but that
> > would be a waste of tools in most cases.
>
> Separation and isolation of concerns is fundamental to managing
> complexity at any level and in any environment. In a computing
> environment it is a fundamental mistake to merge problem solving and
> data storage because they are quite different subject matters with quite
> different invariants.

That is why the separation is already done in the DBMS. The interface
between the application and the DBMS - SQL - doesn't say anything
about data storage.

/frebe

Responding to frebe...

>>>> You then encapsulate those abstractions as a cohesive subsystem behind
>>>> an interface that is tailored to the data needs of your application. If
>>>> you capture the invariants of the persistence paradigms properly,
>>> This is misleading. Databases are about much *more* than just
>>> "persistence". Sure, you can use them for only persistence, but that
>>> would be a waste of tools in most cases.
>> Separation and isolation of concerns is fundamental to managing
>> complexity at any level and in any environment. In a computing
>> environment it is a fundamental mistake to merge problem solving and
>> data storage because they are quite different subject matters with quite
>> different invariants.
>
> That is why the separation is already done in the DBMS. The interface
> between the application and the DBMS - SQL - doesn't say anything
> about data storage.

I think what Bryce was talking about is much more than SQL. It includes
things like stored procedures that are triggered within the DBMS by updates.

SQL itself is very much married to the persistence mechanisms. In
particular, SQL essentially defines the RDB paradigm for storage. SQL is
also designed around a single access paradigm, Query/Join.


--
There is nothing wrong with me that could
not be cured by a capful of Drano.

H. S. Lahman
hsl@pathfindermda.com
Pathfinder Solutions
http://www.pathfindermda.com
blog: http://pathfinderpeople.blogs.com/hslahman
"Model-Based Translation: The Next Step in Agile Development". Email
info@pathfindermda.com for your copy.
Pathfinder is hiring:
http://www.pathfindermda.com/about_us/careers_pos3.php.
(888)OOA-PATH

H. S. Lahman wrote:
> Responding to Jacobs...
>
> This response is directed primarily at the OP. So don't get your hopes
> up that I will participate in one of you chain-pulling exercises.
>
> >> You then encapsulate those abstractions as a cohesive subsystem behind
> >> an interface that is tailored to the data needs of your application. If
> >> you capture the invariants of the persistence paradigms properly,
> >
> > This is misleading. Databases are about much *more* than just
> > "persistence". Sure, you can use them for only persistence, but that
> > would be a waste of tools in most cases.
>
> Separation and isolation of concerns is fundamental to managing
> complexity at any level and in any environment.

Perhaps it's a side issue, but to mirror reality, things *must*
interweave to a large extent, (at least in my domain). Thus, "soft"
isolation tends to be more flexible than hard isolation. If one
becomes an idealist and tries hard isolation, they will be fighting
all day with the messy reality of reality.

> In a computing
> environment it is a fundamental mistake to merge problem solving and
> data storage because they are quite different subject matters with quite
> different invariants.

There you go again, implying it is just about "storage". Repetition of
falsehoods does not make truth. RDBMS could run entirely in RAM and
nobody would know the difference (until somebody trips over the plug
and the data is gone.)

I will entertain the possibility that we simply mean different things
when we talk about "storage".

>
> That can work for niche processing like CRUD/USER where you live because
> the form-based presentation paradigms have been deliberately defined to
> map into RDB structures easily. IOW, the computing can be abstracted
> into a specific RAD IDE paradigm for the sorts of DB/UI pipeline
> applications where the major problem being solved is data entry and
> presentation. That is, the user does the data analysis and reacts to it
> once it is in a convenient format, not the application.
>
> But for applications that solve complex business problems that involve
> actually analyzing and processing the data it just leads to
> unmaintainable applications.

I keep asking for specifics, and you never give any. Some have
mentioned graph traversal, which as a practical matter I agree, but in
theory could be added to RDBMS, and does exist to some extent in some
products. But to equate "complex" with graph-traversal is misleading.
And there may be other classes of problems that RDBMS are not well-
suited for. However, "complexity" is not the difference maker.

> In those cases the concerns need to be
> separated. You can use DBMS-based tools for things like reporting on
> inventory activity, but if you try use such tools to manage the
> inventory it would be a maintainability disaster in the long term.

RDBMS *are* used to manage inventory.

> Not
> to mention that the table-level query/join philosophy would be
> horrendously inefficient for the solution since the same data is likely
> to be accessed repeatedly during algorithmic processing.
>

Specifics? Examples?

>
> --
> There is nothing wrong with me that could
> not be cured by a capful of Drano.
>
> H. S. Lahman

-T-

Responding to frebe...

>> IOW, your problem solution doesn't care if the data is stored in an RDB,
>> OODB, flat files, or clay tablets. The problem solution just says,
>> "Here's a pile of data I call 'X'. Store it so that I can get it back
>> later by asking for 'X'".
>
> In this case, you would only need one table in your database
>
> create table theonlytablethatiseverneeded
> (
> id integer,
> data blob
> )

That's pretty much the point. From the problem solution's perspective
there is only one set of data. The problem context doesn't care if there
is another problem context with a set of data for "Y" and that set
partially intersects with the "X" set.

The DBMS paradigms are designed for general, ad hoc, problem-independent
data storage where one needs to sort out things like intersecting sets
of data when there are multiple clients.

Corollary. It would be silly to use an RDB when the data was accessed by
only a single application with a single query or multiple
non-intersecting queries. It would ALWAYS be more efficient to use flat
files for your "data blobs". An RDB would only become viable if the
application internally needed different sets of data in different
solution contexts AND those sets intersected.



--
There is nothing wrong with me that could
not be cured by a capful of Drano.

H. S. Lahman
hsl@pathfindermda.com
Pathfinder Solutions
http://www.pathfindermda.com
blog: http://pathfinderpeople.blogs.com/hslahman
"Model-Based Translation: The Next Step in Agile Development". Email
info@pathfindermda.com for your copy.
Pathfinder is hiring:
http://www.pathfindermda.com/about_us/careers_pos3.php.
(888)OOA-PATH

On 10 Nov, 23:34, "H. S. Lahman" <h...@pathfindermda.com> wrote:
> Responding to frebe...
>
> >> IOW, your problem solution doesn't care if the data is stored in an RDB,
> >> OODB, flat files, or clay tablets. The problem solution just says,
> >> "Here's a pile of data I call 'X'. Store it so that I can get it back
> >> later by asking for 'X'".
>
> > In this case, you would only need one table in your database
>
> > create table theonlytablethatiseverneeded
> > (
> > id integer,
> > data blob
> > )
>
> That's pretty much the point. From the problem solution's perspective
> there is only one set of data. The problem context doesn't care if there
> is another problem context with a set of data for "Y" and that set
> partially intersects with the "X" set.

This approach is indeed used a lot by OO people. But what about
referential integrity for example? Unnecessary feature? What about
indexes and possibilites to find data using different criterias? Using
your approach you would basically have to build indexes in your BLOBs.

> The DBMS paradigms are designed for general, ad hoc, problem-independent
> data storage where one needs to sort out things like intersecting sets
> of data when there are multiple clients.

DBMS:s are indeed build for multiple clients. But are you trying to
suggest that they are ONLY build for "general, ad hoc, problem-
independent data storage"?

> Corollary. It would be silly to use an RDB when the data was accessed by
> only a single application with a single query or multiple
> non-intersecting queries.

Not many applications have one single query....

> It would ALWAYS be more efficient to use flat
> files for your "data blobs".

Even a single query could be rather complicated and if you have
multiple users, your flat files would quickly prove to be more
inefficient to use.

> An RDB would only become viable if the
> application internally needed different sets of data in different
> solution contexts AND those sets intersected.

As pointed out above, not many applications have only one query.... I
would also add that RDB would become viable as soon as you have more
than one user and/or care about data integrity.

/frebe