What are the keys too good db design?

[Cogman]

So right now, I'm faced with the merger to two very different design philosophies for databases.

For my company, the philosophy of the DB is something to the effect of "It stores data, Anything else should be handled in the application (With the exception of maybe simple adds or sums)."

The company that I am trying to design an interface took a design philosophy of "If the database can do it, it should do it. Applications should only get information out of the database and should change the data as little as possible".

So what are the design principles of a good database? Which philosophy is the most correct?

 

[Rangoric]

I'd say a mix. There are things the DB will be good at (manipulating data, playing with data) while there are things that the Code will be good at (Dealing with a small piece of data).

However I wholeheartedly disagree with the second approch. With MS SQL Server 2005 you can shove .Net Assemblies into the Database and have the app literally just read in and spit out data. That makes me want to cry just thinking about having to work with it.

 

[BoberFett]

Depends entirely on the project. You need to look at the project as a whole and determine what is important. Is it for a small limited use application for a few users or will it be used for large scale B2B applications? How important is data integrity? Is it mostly single tables with a few lookup tables sprinkled around or will you have complex relationships where transactions are of utmost importance? Can the business rules be implemented efficiently in SQL or is that best left to a separate tier? What are the strengths of the developers who will be working on this database and it's related apps?

There's no way to make any generalizations about this kind of architecture decision.

 

[Markbnj]

Maybe it's just me, but I view databases as a place to store data, not logic engines. Their job is to evaluate relational algebra and return sets. That doesn't mean business logic never gets implemented in the database. But when it does it is usually an optimization. Database vendors, of course, have been trying for years to evolve them into application servers.

 

[Cogman]

Originally posted by: Markbnj
Maybe it's just me, but I view databases as a place to store data, not logic engines. Their job is to evaluate relational algebra and return sets. That doesn't mean business logic never gets implemented in the database. But when it does it is usually an optimization. Database vendors, of course, have been trying for years to evolve them into application servers.

Well, and that is the good thing with my company treating the databases as such. By doing so, they don't bind themselves down to a certain database. That way, a company that invested their big bucks in an oracle server doesn't have to be told "Well, our software only works for MS Sql 2005". Thus avoiding the headache of having to setup a new database server for every client.

 

[GeekDrew]

I sort-of agree with Markbnj - I'm all for enforcing referential integrity in the database (through constraints and etc - when it's actually possible to do so according to business rules, which is frequently not the case).

 

[brianmanahan]

A lot of unnecessary views and stored procedures tie your app so tightly to a database that you'll likely never get out without a complete data translation & app rewrite. I've seen it done in DB2. It's sad, just really sad. The app is basically stuck in DB2 version 8 forever.

 

[chronodekar]

Originally posted by: Cogman

Originally posted by: Markbnj
Maybe it's just me, but I view databases as a place to store data, not logic engines. Their job is to evaluate relational algebra and return sets. That doesn't mean business logic never gets implemented in the database. But when it does it is usually an optimization. Database vendors, of course, have been trying for years to evolve them into application servers.

Well, and that is the good thing with my company treating the databases as such. By doing so, they don't bind themselves down to a certain database. That way, a company that invested their big bucks in an oracle server don't have to be told "Well, our software only works for MS Sql 2005". Thus avoiding the headache of having to setup a new database server for every client.

I've heard of being locked-in to a database, but the way you put it, makes a lot of sense. I have to agree with your design philosophy.

 

[KentState]

My approach has always been to keep the number of transactions down between the client and application. Meaning that you aggregate the data the best you can before sending it and make sure you can cache data locally if it makes sense. I also like keep enough of the logic in the database so that smaller business rules don't require a recompile of the application. Much easier to push out a few table and procedure changes, than a new version of the app.

 

[BoberFett]

Originally posted by: KentState
My approach has always been to keep the number of transactions down between the client and application. Meaning that you aggregate the data the best you can before sending it and make sure you can cache data locally if it makes sense. I also like keep enough of the logic in the database so that smaller business rules don't require a recompile of the application. Much easier to push out a few table and procedure changes, than a new version of the app.

That depends. Anything using a SOA should be pretty much server side as well. As long as the DB is locked down and all accesses are via the SOA it's not much more work to upgrade. And it can be far more robust than purely DB driven logic.

As I said, it all depends on the needs.

 

[ForumMaster]

we mostly use oracle for out databases because of size. i have quite a few plsql functions that do certain logic that i can't do in code or more correctly that would be a nightmare to do in code. the database has support for things like triggers, functions and other things that can allow it to do things that are much harder to do from an application side. on the other hand, start doing to much processing on the database side, and you're just slowing your database for no reason.

to know what a database can or rather should do, you need a good dba. and good dba won't want everything outside of the database. he'll tell you what can be done and what shouldn't be done. do you have an experienced dba?

while i agree with Markbnj that not using the db for anything else then data storage frees you from a database vendor, using the abilities of a db properly can be very useful.

 

[KIAman]

From my experience, the key to a good database design depends on if there is user customizable and dynamic content on the front end or not.

For example, a building a large CMS (content management system) for a website would require much of the processing and logic to be on the database. The front end would simply call simple boolean checks to display dynamic data without caring about what the bool is checking for or what the data actually consists of.

Other examples are personal urls,survey system, a FAQ system, etc. The front end should be written in such a way that it only is a portal to custom data while the database does the majority of the logic.

If the application does not allow user customizable or dynamic data, then the database should be kept very light and simply used as a data storage medium (which it was designed for) and let the front end do logic and processing. This allows the mostly static front end to be easily ported to different RDBMS without much issues.

 

[Markbnj]

Originally posted by: BoberFett

Originally posted by: KentState
My approach has always been to keep the number of transactions down between the client and application. Meaning that you aggregate the data the best you can before sending it and make sure you can cache data locally if it makes sense. I also like keep enough of the logic in the database so that smaller business rules don't require a recompile of the application. Much easier to push out a few table and procedure changes, than a new version of the app.

That depends. Anything using a SOA should be pretty much server side as well. As long as the DB is locked down and all accesses are via the SOA it's not much more work to upgrade. And it can be far more robust than purely DB driven logic.

As I said, it all depends on the needs.

This is the architecture I find myself working with the most lately. Since the services are on a gigabit link to the database transactions are very fast, and we see little need to drive routine business logic to the DB. The DB does execute logic, most often related to some complex searches, but in our world it is the services that are responsible for getting raw data from the db, applying busines rules to it, and returning business entities to the clients.

 

[PricklyPete]

I hate to make general design guidelines because in reality a guideline tend to keep people from making good judgment calls.

In general I like to keep my db designs flexible and only contain simple business layer logic that will improve the interface with the db.

I friend of mine worked for a company where almost their entire business layer was at the stored procedure/trigger level and they ran into major issues as they attempted to grow. I'm not saying it is bad...but I think they took a general guideline and over did it.

 

[BoberFett]

Originally posted by: Markbnj

Originally posted by: BoberFett

Originally posted by: KentState
My approach has always been to keep the number of transactions down between the client and application. Meaning that you aggregate the data the best you can before sending it and make sure you can cache data locally if it makes sense. I also like keep enough of the logic in the database so that smaller business rules don't require a recompile of the application. Much easier to push out a few table and procedure changes, than a new version of the app.

That depends. Anything using a SOA should be pretty much server side as well. As long as the DB is locked down and all accesses are via the SOA it's not much more work to upgrade. And it can be far more robust than purely DB driven logic.

As I said, it all depends on the needs.

This is the architecture I find myself working with the most lately. Since the services are on a gigabit link to the database transactions are very fast, and we see little need to drive routine business logic to the DB. The DB does execute logic, most often related to some complex searches, but in our world it is the services that are responsible for getting raw data from the db, applying busines rules to it, and returning business entities to the clients.

I think that's the way the world is going. Right now the cloud is mostly hype, IMHO, but for portability and interopability I think we're going to see most important business software move to the SOA model, especially for B2B.

But that's just this analysts view.