recommended practices? dealing with the database-application divide

[Ryan Bobko <ry...@ostrich-emulators.com>]

Hi Folks,
This is something I seem to struggle with every time I write a new db
application. I feel like I design the database logically, but somewhere
in the application code, I always seem to define constants that directly
mirror what's in my database. I'm interested in how other people deal
with this, and/or if there's something I should be doing to avoid the
situation.

For example, suppose I have a two tables for dealing with users:

create table user( id int primary key, name varchar(20), level int );
create table privilege( id int primary key, desc text );
insert into privilege( id, desc ) values( 0, 'user' );
insert into privilege( id, desc ) values( 1, 'adminstrator' );

Then in my code, I can get users and their level, but somewhere I need
to define what the levels are. I seem to have statements like:

public static final int ADMIN=1;
if( ADMIN==user.level ){...}

Obviously, this is a simple case where I could just as easily add an
"isAdmin" column to the table, but what if I had ten different levels?

One of the things I like about using the database is that I can change
values easily, but that is of no value when the app is so tightly
coupled with the database. Any solutions out there?

Thanks for your time,

ry

Re: recommended practices? dealing with the database-application divide

[David Van Couvering <da...@vancouvering.com>]

IMHO, the best way to reduce the coupling between the database and
your application code is to create a "model" abstraction.  This
abstraction provides an application-friendly view of your data, and
allows you to inject, as it were, code that manages the differences
between your application-friendly abstraction and the actual data
model.

I would also highly recommend using an object/relational mapping
technology such as a Java Persistence Architecture provider (Apache
OpenJPA, Toplink, etc.), JDO or any number of others (JPA seems to
have the most traction these days).

These technologies give you a nice mapping between objects and data.
But this doesn't negate the need to place a higher-abstraction layer
(something I have heard call the "model facade") that gives you a
place to manage the difference between what you want your application
code to see and how the data is modeled in the database.

David

On 6/1/07, Ryan Bobko <ry...@ostrich-emulators.com> wrote:
> Hi Folks,
> This is something I seem to struggle with every time I write a new db
> application. I feel like I design the database logically, but somewhere
> in the application code, I always seem to define constants that directly
> mirror what's in my database. I'm interested in how other people deal
> with this, and/or if there's something I should be doing to avoid the
> situation.
>
> For example, suppose I have a two tables for dealing with users:
>
> create table user( id int primary key, name varchar(20), level int );
> create table privilege( id int primary key, desc text );
> insert into privilege( id, desc ) values( 0, 'user' );
> insert into privilege( id, desc ) values( 1, 'adminstrator' );
>
> Then in my code, I can get users and their level, but somewhere I need
> to define what the levels are. I seem to have statements like:
>
> public static final int ADMIN=1;
> if( ADMIN==user.level ){...}
>
> Obviously, this is a simple case where I could just as easily add an
> "isAdmin" column to the table, but what if I had ten different levels?
>
> One of the things I like about using the database is that I can change
> values easily, but that is of no value when the app is so tightly
> coupled with the database. Any solutions out there?
>
> Thanks for your time,
>
> ry
>