use OOP to design database tables schema?

[Stan James]

Can you please explain what you mean by "database driven applications"? Thanks!

See PowerBuilder.

[Murasoli Maran]

Originally posted by Ilja Preuss:


Can you please explain what you mean by "database driven applications"? Thanks!

Most applications which help businesses are database driven.i say 90%.

"database driven applications" means applications which needs persistance most.for example,a CRM application.everything is stored in database and insert-retrieval is the basis of application.data is colleced from user and inserted into DB with some calculations or modifications in domain layer.Later data will be retrieved from DB and shown to the user with some view coding.

Most applications are like this.concider a web shopping cart application,what the system doing?.collecting data from user and inserting to DB and vice versa.what else?.there will be some calculations in middle and some web container settings.there is not much 'else'.ofcource MVC will be there,but no need to say abt it.MVC is a pattern only and i can use MVC if i start with database also.

And one member said "costomer will take care only functionalities".what i say is "what functionality you will loose if u start with database design"?.Functonalities can be provided.It's based only on requirements.Functionalitied does'nt need OOAD.Probloms can be solved in many ways.And there is not much "Patterns" for all the probloms.It should be dealt with intelligence.

Applications like web-shopping cart should be said as "database driven application".database design is most important for an application like this.Plz tell me "what benifits you get by starting it with OOAD??".I can defenitely tell you what i benefits i get if i start with DB design rather than OOAD.How you are going to map subclasses into DB tables?.So say ur answer simple.Dont maniplulate with buzzwords.Talk simple.And i also welcome if you educate me anything.I welcome all learning.It'll be good for me.

Most POS applications still run in Foxpro and other databases with non window based UI and OS.a POS system can also an example of "database driven applications".as i told,90% business applcations are.Even if you provide a wrapper of GUI windows to POS,it's still "Database Driven".

I think i have told my answer decently to ilja preuss.And i hope i can study a lot from bartenders in this issue.

Make it simple as possible.So i can understand.Teach me,i wanna learn.

thanks,
MM

[Murasoli Maran]

Originally posted by Stan James:


See PowerBuilder.

Only testing

[Warren Dew]

Murasoli Maran:

Applications like web-shopping cart should be said as "database driven application".database design is most important for an application like this.

I think if one were doing a web store, and not just the shopping cart portion, one would find that presentation of the data to the user is just as important as storing his input to the database. Certainly that's been the case in the web applications I've worked on.

What I've found is that I often want to use a single presentation method for multiple types of data, or present the same data in multiple ways. In the former case, I generally want to be able to reuse the same UI class for data from diverse portions of the database. In the latter case, I may want to have several different classes that correspond to the the same portion of the database, or at least to overlapping portions of the database.

For these reasons, I don't think it's generally a good idea to have a strict one to one correspondence between the class design and the database design. And once one realizes they need to be different, and there needs to be translation between the two, it doesn't matter which one is done first.

[Stan James]

The purest OOAD starts with objects and their behaviors. Focusing on behavior instead of data gives a very different object design. You can ask an object to do a lot of interesting things without knowing what data it uses or where it gets it. This approach works out very nicely in stateful clients that load everything up in memory and make it interact. Objects really shine compared to procedural code in this kind of app.

But stateless web servers are totally different animals. For most of these I'd start with a solid data model, an IO module for each table, a business module for each business function, some dto structures to move things in and out of the UI. It would look almost EXACTLY like mainframe COBOL apps I built in the 80s. An object oriented language is almost an afterthought.

Was that inflamatory enough?

[Fintan Conway]

Originally posted by Edward Chen:
This question has haunted a while.

Usually, we firstly check the porject requirements and set up tables, and then do 1/2/3-NF normaliztion.

I don't like this way, because it is not Object-oriented way. So any body could share exprience how we use OOP to design complicate table schema/relationship ?

Even a link/book ISBN is welcome. That is very important for me.

Thanks

Hi Edward,

Java Data Objects (JDO) would be the solution that you are looking for. This is a technology where you create your objects as normal. Then you run an "enhancer" on your classes. The JDO implementation creates corresponding tables in the DB, and maintains the links between your classes and the database tables. By default there is a one-to-one link between your class and a DB table.

If the DBA needs to optimise/tune the tables that were created (refactoring??), you can modify the mapping of fields in your class to fields in the tables so your classes and the db remain in synch. Doing things like creating indexes will not affect this mapping.

Regards,

Fintan
[ July 16, 2004: Message edited by: Fintan Conway ]

[Warren Dew]

Fintan Conway:

Then you run an "enhancer" on your classes. The JDO implementation creates corresponding tables in the DB, and maintains the links between your classes and the database tables. By default there is a one-to-one link between your class and a DB table.

A tool that automatically converted the database tables to Java classes would save a lot more work, and probably be as effective.

[Eric Lemaitre]

Hi all !

What is this ???
There are allready about 30 posts, and no one stated "JDO" even once ?
Please be all conscious that JDO stands for Java Data Objects, is a Java standard agreed by Sun, and of course involves ORM (Object Relational Mapping). So it looks very strange to me it was not stated before for such topic.
Better late than never anyway. Of course JDO is the best way to deal about data when programming in Java, some others are good too (hibernate, toplink, castor, ...) but only JDO is Java official standard.
There are many ways to test such mapping, so I recommend you use some mapping tools so as to test by yourselves. The one I use is LiDO (http://www.libelis.com/) for you have a "defineSchema" tool which is available for free in "community" edition of the product. You will have to register of course so that their sales guys can hunt you down , but of course you can refuse to get email offers at registration, the product is complete but limited to 2 PersistenceManagers, far enough for testing. As MySQL or PostGreSQL are useable, one can say this one is for free.
Now JDO has evolved into separated metadata file (say what you want persisted) and mapping file (say how you want it persisted). Introspection is used too so that metadata can be minimum (such as <Person/> so as to persist whole class including all its fields).
It is truly the most edge cutting modelling technology right now, totally devoted to Java, and standard, so I simply had to interfere.

Best regards.

[Eric Lemaitre]

Hi all !

Oops, sorry , I was directly led by JavaRanchJournal to first page and didn't notice there was a second page involved.
Of course JDO was stated, it had to be for this post, and in the right way "JDO is the technology you are looking for".
JDO is often rightly nicknamed as "Just Do Objects" (no DataBase modelling).

So sorry again guys, I will read one $Soft related article as punishment .
But what a long post ..........

Best regards all.

[Ilja Preuss]

Originally posted by Stan James:
But stateless web servers are totally different animals.

Are they? Are they also "behaviourless"? Or are they just a different frontend to the same behaviour/business logic?

Perhaps I am misunderstanding what you mean by "stateless"?

[Ilja Preuss]

Originally posted by Murasoli Maran:
"database driven applications" means applications which needs persistance most.for example,a CRM application.everything is stored in database and insert-retrieval is the basis of application.data is colleced from user and inserted into DB with some calculations or modifications in domain layer.Later data will be retrieved from DB and shown to the user with some view coding.

I don't like to call those applications "database driven" because the database should, IMO, most often be an implementation detail. That is, the user typically won't care where and how the data is stored.

That's *one* of the reasons I don't like my whole application to be driven by the form of persistence - I might later want to change the persistence (connect to some strange old data store, or use some cool new technology), and then I don't want the rest of the application to be affected.

Most applications are like this.concider a web shopping cart application,what the system doing?.collecting data from user and inserting to DB and vice versa.what else?.there will be some calculations in middle and some web container settings.

Yes, there will be "some" calculations in the middle, and probably "some" decisions to be made. And I would actually argue that this part - the business logic layer - is a quite important part. After all, only storing and displaying data is quite trivial, the value comes from *doing* something with the data, doesn't it? And that layer most often can benefit from OO.

And one member said "costomer will take care only functionalities".what i say is "what functionality you will loose if u start with database design"?.

None. You only loose flexibility in implementing it - at least if you let the business logic code be driven by the database design, as you seemed to suggest iirc.

Functionalitied does'nt need OOAD.Probloms can be solved in many ways.And there is not much "Patterns" for all the probloms.It should be dealt with intelligence.

I fully agree. OOAD is just a tool to manage dependencies. A quite important tool, in my opinion. That's why I typically want to use it.

Applications like web-shopping cart should be said as "database driven application".database design is most important for an application like this.

What's so important about the database design for those applications?

Plz tell me "what benifits you get by starting it with OOAD??".

Simpler code by better dependency management. For example, introducing a new variation of behavior by pluging in a new class, instead of having to change if- and switch statements in a bunch of places scattered over the code.

How you are going to map subclasses into DB tables?

Depends on the specific case. Do you want to imply that this is hard?

Most POS applications still run in Foxpro and other databases with non window based UI and OS.a POS system can also an example of "database driven applications".as i told,90% business applcations are.Even if you provide a wrapper of GUI windows to POS,it's still "Database Driven".

Yes, those applications exist. But, are they easy to maintain and extend?

[Stan James]

But stateless web servers are totally different animals.

Are they? Are they also "behaviourless"? Or are they just a different frontend to the same behaviour/business logic?

Perhaps I am misunderstanding what you mean by "stateless"?

By stateless I mean I'm not going to make an employee object on the server and have three calls from the browser that say setHoursWorked(September, 100); setHoursWorked(October,200); setHoursWorked(November,300); and keep that state in memory in some sub-object of employee, as I most surely would in a fat client. I can't afford the memory and I have a dozen load-balanced servers that make it prohibitively tricky to synchronize in-memory objects.

I'm more likely going to have a path end-to-end through the architecture that puts one month's hours in the database. The behavior will be a little object that takes data from the screen and puts it in the database.

And with a bunch of little behavior objects, I won't have a beautifully intricate object model to drive my data design. I can just do a good data design period. When it's time to compute hours worked on project X the data model may let me do it in a single query. Then the data-first method wins ... for a day.

One of the gurus (Martin Fowler? Robert Martin? Martin Martin?) has a paper on the negative effect of data driven design and poor object models. It's a great rant on the evils of these little behavior objects. My project just moved from fat client to browser/web and the meaningful object model was a sad casualty.
[ July 17, 2004: Message edited by: Stan James ]

[Murasoli Maran]

Thanks all.

MM

[Tim Twe]

Two rules I apply,

Rule 1: Model configuration as object models
Rule 2: Model historic information via data models


Databases are good at historic information and if you think this out correctly you're reporting on historic information is easy. (Data Model First)

Managing all that configuration is messy. At some point you've got an object model. Mightaswell make sure that the object model maps to the relational model correctly. (Object Model First)

-Tim

[Scott Ambler]

I think you may find www.agiledata.org/essays/agileDataModeling.html to be of interest. It walks through an example where the data model is developed in an agile manner yet it is not completed up front (some up front domain modeling is done, but very little).

Doing a data model up front is a choice, it isn't a necessity.

- Scott

[Rikard Qvarforth]

how does Prevayler fit in to this ?

//1.618

[Lasse Koskela]

Originally posted by Rikard Qvarforth:
how does Prevayler fit in to this ?

It doesn't, I think, because it doesn't involve a database schema.

[Steve Quintana]

This topic seems to be dying off but I just want to throw in my 2 cents. I think that OR mapping tools are around mostly because it is a neat thing to do that appears to make the programmers life easier at the expense of performance and proper design. Someone earlier said that DB design and OO design are driven by different requirements - I think this is partially true except that the DB schema is one of the requirements that the OO design must meet. In other words, the OO design is outside the scope of the DB design, but the DB design is a consideration in the OO design. Hence, these tools, while convenient to the OO designer, should not be used for large scale production systems.

A much better solution, I've found, is using DB schema to Java code generators. I'm working on such a tool right now that provides a basic scripting language that allows one to use the DB schema to generate Java code. This allows precise control of the code generated and allows the creation of an intermediate layer so that the OO design can use it and be that much more (but never entirely) free of DB design considerations. Cheers,



Steve Q.

[Ilja Preuss]

Originally posted by steveq9t4:
I think that OR mapping tools are around mostly because it is a neat thing to do that appears to make the programmers life easier at the expense of performance

Three things:

First, this is not necessarily a bad thing. Hardware is getting cheaper and cheaper at rapid rates, but software development remains to be costly, software maintenance doubly so. Typically a customer will be much more willing to live with a system that is half as fast than one that is twice (or likely more) as costly.

Second, it is not necessarily true. A good persistence framework will be able to do database specific performance optimizations that the developers might not even be aware of.

And last but not least, the mapping between the object model and the database model is only a rather small part of database performance. In my experience it is much more affected by the database model itself, by the proper use of indices etc., by the configuration of the database server and by network communication constrains.

And should you encounter a performance problem because of the mapping, a good framework should give you tools at hand to handle it, such as tweaking the mapping, lazy loading, caching or even circumventing the mapping in special cases.

and proper design.

Could you please elaborate on how decoupling the OO desing from the data design leads to improper design?

the DB schema is one of the requirements that the OO design must meet. In other words, the OO design is outside the scope of the DB design, but the DB design is a consideration in the OO design. Hence, these tools, while convenient to the OO designer, should not be used for large scale production systems.

You say that, but I don't see *why* that would be the case. Could you please elaborate?

A much better solution, I've found, is using DB schema to Java code generators. I'm working on such a tool right now that provides a basic scripting language that allows one to use the DB schema to generate Java code. This allows precise control of the code generated and allows the creation of an intermediate layer so that the OO design can use it and be that much more (but never entirely) free of DB design considerations.

What does this intermediate layer look like and how is it used from the OO code?

[Steve Quintana]

Hi Ilja, you made some good points.

A couple of things though, the hardware cost argument is an interesting one in that it basically states that the customer is willing to live with a suboptimal design as long as the costs are "lower" (lower than what?). I agree completely but this argument is so broad that it is virtually meaningless. It could be applied to anyones convenience at any point regarding any design decision that leads to a little less development time.

Regarding the persistence framework optimizations, yes there are some optimizations that can be done automatically that most developers would be unaware of. However, the framework cannot possibly know anything about the context of the data. In particular, normalizing too much degrades performance, sometimes greatly, sometimes insignificantly. Would a framework know where to draw the line?

Regarding your third point, "much more affected by the database model itself, by the proper use of indices etc., by the configuration of the database server and by network communication constrains" Absolutely agreed.

About the "proper design" comment, forgive me, it is a little harsh. I was merely referring to determining the DB structure from a convenient (to the programmer) OO design - just expressing my opinion that I find that backwards.

Regarding the code generator, there really is no specification for the intermediate layer. The tool I'm working on provides a scripting language that uses DB metadata to generate code according to a script. The script looks like a regular source code file (Java in my case but could be C, C++ etc.) with tags sprinkled throughout. Here's a sample method in Java:

public List getTextColumns() {
List l = new ArrayList();
{foreachfield/}
{if field.istext/}
l.add("{field.name}");
{/}
{/}
return l;
}

By the way, I use {tag/} to indicate a tag that has a body. The body is terminated by a {/} tag. This scripting allows me to generate code for databases to conform to any interface needed. Note also that there is no need to learn any new language or framework, the code is essentially in the language the programmer understands with a few simple tags added in (and no XML to boot . Furthermore, changes to the database structure may be isolated to this file depending on the code generated and how it is used. Something I am going to be looking into soon is handling complex DB structure changes over time and caching. An interesting project, to say the least. Cheers,

Steve Q.

[Ilja Preuss]

Originally posted by Steve Quintana:
A couple of things though, the hardware cost argument is an interesting one in that it basically states that the customer is willing to live with a suboptimal design as long as the costs are "lower" (lower than what?).

I would actually argue that the whole point of design is to minimize costs. Why else would we care about it?

It could be applied to anyones convenience at any point regarding any design decision that leads to a little less development time.

Yes, unless other costs trump the decision. Doesn't that sound reasonable to you?

(Notice that I actually care most about *long term* development costs, which I'd think a persistence framework, or any other form of decoupling between the application and database design for that matter, would provide.

the framework cannot possibly know anything about the context of the data. In particular, normalizing too much degrades performance, sometimes greatly, sometimes insignificantly. Would a framework know where to draw the line?

I am not arguing for a framework automagically generating the database design from the object design - far from!

What I am advocating is for humans designing the database and object designs independently of each other and then use a persistence layer to map one to the other.

public List getTextColumns() {
List l = new ArrayList();
{foreachfield/}
{if field.istext/}
l.add("{field.name}");
{/}
{/}
return l;
}

This looks interesting. I wonder, though - what's the important difference to using a persistence framework which basically would replace your scripting with regular method calls?

Thanks for the interesting discussion!

[Jester Jaimi]

Some heavyweight mathematician (I think it was Bertrand Russell) once said...

"All models are wrong, but some models are useful."

Being a Consulting Jack of All Widgets, I have been looking at this problem for a while, and I have come to the conclusion that while there are no absolutes, there are proven approaches and best practices that can aid us in our never ending quest for technical nerdvana. While I find OOA/OOD fascinating, as far as modeling goes, it is a bit of a hair ball in terms of complexity. The reason I prefer the data modeling into agile development approach is twofold. Most of my Hard Core Java friends have stylistic Agile/XP preferences, and for those proud few who can code at 50+ variables per minute, it is easier to implement objects from the bottom up and test/refactor them against a fairly normal data model than it is to delay coding in order to haggle over an object model that doesn't actually do anything.

Secondly, while deceptively similar in appearance, pure data model's, being methodless, eventless and "relatively" stateless, are infinitely simpler than object models, so getting the initial ER design correct is something that can happen in relatively short order when compared to the full UML design lifecycle. Then once you have a "Useful" data model (meaning rational, relatively normal and relatively abstract) developing/refactoring the brainiac OO code to interact with your clean ER design is a fairly straight forward process. And if you have done your homework by abstracting the I/O between the Object Relational divide using interfaces, you should be able to refactor the data model after the fact as well, provided that the signatures of your Object Relational accessor/mutator class methods don't change either structurally or semantically in the refactoring process. I know, BIG IF, but that is the concept anyway.

The final part of the process is mapping the objects against database entities. There are numerous solutions for this, from Hibernate to Castor to JDO to good old JDBC. And while I am here, I will even dare to mention the dreaded old school View/Stored Procedure approach. Most of my Java pals cringe when I talk procedural like this, but I have to say, while it's old school fer shure, it works, and it's an approach that's been around for over a decade. In fact, Oracle has managed to upgrade most of it's customers to the latest version of their database by hiding the implementation details of Oracle behind a data dictionary which is implemented as a collection of views, without hosing their install base in the process. And if it works for something as complex as Oracle, it's probably good enuf for little old me. Yet, regardless of the approach you choose, the mapping part of the exercise is largely an implementation detail that should be relatively painless to execute, provided the data model is well thought out and the code base is cleanly implemented.

The one thing that gets overlooked in these conversations is the fact that RDBMS technology is infinitely more powerful than just a convenient way to simply store the information contained in objects. In fact, being a bit of a DB bigot, between IBM's DB2, Oracle PostgreSQL and all of the other vendors, I would wager that there have been more person years of R&D invested RDBMS technology than all of the in vogue OO platforms combined. What I am getting at here is that the Java community, in it's verve to stay in step with the latest technical fashion, seems to be intent in throwing the RDBMS baby out with the bath water. It's like the old adage says, when all you have is a hammer, everything looks like a nail.

While my SA chops are seriously lacking these days, I have an old school Unix attitude. Different tools are best suited to solve different problems. In relation to this thread, there are application logic bits that are best suited to be implemented as objects, and there are data logic bits that are best managed by the database. If your Object Relational design is elegant, then determining exactly what goes where can be an exercise in splitting hairs, but if you dare to buck the trends and use common sense whilst designing and implementing your apps, in 2005 you should be able to develop something you can live with, if you are mindful and reasonably rigorous throughout the full Software Development Lifecycle Process.

There is one last tid bit before I go. The one thing I agree with relative to the Java community is that from the OO side of the object relational equation, Object Oriented Components should regard the database as a Black Box Object as much as possible. Likewise, beyond authentication, the database should know little or nothing about the applications that interact with it. Application logic should be encapsulated in the object layer, database logic should be encapsulated in the database as views and/or stored procedures, and all efforts should be expended to limit the scope of OO and RDBMS controlled logic in their respective places in the system stack, with minimal cross cutting concerns or dependencies between the Object/Relational parts of the system. If the architecture you are pursuing in your design/implementation efforts allow for this level of well scoped logic partitioning, regardless of whether you implement it using tech chic or old school techniques, then chances are good that you are heading in the right direction.

Jaimi Becker
Data Modeler, Postgres DBA, i18n/m17n Consultant, Internet Engineer
Yamaha Music Interactive, Inc.
http://music.yamaha.com

Last Published Article: http://www.oracle.com/database/feature_db_progressive.html
Latest Internet Baby: http://costumenetwork.com

[Ken Loh]

The argument of whether to start designing the DB first or the business logic first has always been interesting. I have experienced both the db-first way and the business-logic(aka feature) way - with the former sank like the Titanic ! But if you start thinking that I've voted outright for the business-logic-first camp, read further and think again.

After various post-mortem for our Titanic, we discovered that designing the DB first would work well to model the documents in used e.g. receipts, invoices, delivery order, purchase order... . The application upon completion seems to suit the documents, rather than the users and the actual workflow. We had built a document management system that our client did not ask for ! Oh man... I can't blame you if you think Titanic is an understatement!

In another project, we did it in another way after feeling the earlier pain. We built the business model first, followed by the DB's. You can say that we followed extensively what RUP recommends.

In the process, we discovered many redundant procedures that had been performed blindly for many years by the users. We discovered many documents should be either done away with or integrated with others.

As a result, DB tables which we could have thought would be required, were dropped while those that we'd never thought we needed, were created. The DB is now more in-tune with the business of the users than in my other project that had kaputed.

Have we done any refactoring to the database ? Well, put it this way... now none of us know how to construct the query "ALTER TABLE" without referring to an SQL reference.

Therefore, is business-design-first method the silver bullet ? Even though, we have gained success using this method, we still believe that a silver bullet doesn't exist. Who knows, one of these days, a project of a kind that we have not experienced before may require a db-first approach. The software world is full of wonders. Aren't we all lucky !
[ February 24, 2005: Message edited by: Ken Loh ]