Object Oriented Design between application and database?

Question

4.50/5 (2 votes)

See more:

, +

Hi,
Since last week I have been looking into proper object oriented design. I have read about the SOLID principles and some Design Patterns which use these principles. After a week of reading and practicing I feel I have a basic understanding of the principles. As I am progressing in my study I cannot help but feel these SOLID principles and Design Patterns do not address a very important issue in software design: database interaction.
Interaction with a database is important for almost any application. But when would I put some business logic into my application design and when would I handle it in my database? I have been reading about reusability of code, but when I use a stored procedure to do some math I cannot count on that SP to be reusable in another application (that might use another database). Putting certain logic in a database might really speed up performance of the application though.
To give an example, my company used to calculate prices for a certain product for a certain customer on a certain date and a certain amount in code. This took several seconds. When putting the calculation in the database it took only a few milliseconds.
Whenever I look at SP's in SQL2008 I do not see any of the SOLID principles though. And if I would I would most certainly go crazy because having to find around 10 functions and SP's to do a single calculation in SQL is not as easy as in Visual Studio.

So what I would like to know is if there are any SOLID database principles and SOLID application-to-database principles to make maximum use of the regular SOLID principles.
I do not need detailed explanations. Some references to articles, books and/or websites that address this issue is enough. But right now I would not know where to look :)
Thanks.

Posted 4-Mar-11 23:37pm

Sander Rossel

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

milenalukic · Accepted Answer · 2011-03-05T08:07:00

Solution 1

To be able to use database calculations effectively first you must have a normalised database with all the relevant information. From there progress to create a stored procedure to do your calculations. this will take the required parameters and return only the required results, which will reduce your network traffic. Test your SP in SQL first.

From visual studio all you will need to do is to create a Linq to SQL and connect to the sp and use the results of the sp.

For use in another application I suspect that you will be using another db for some of the parts and then require the price calculation from the first db. All you will need to do here will be to create a second linq file.

Posted 5-Mar-11 8:07am

milenalukic

Comments

Sander Rossel 6-Mar-11 3:47am

Thanks for your answer. However, I think you misunderstood what I meant.
The point is not that I do not know how to make, test and implement an SP. The point is WHY and WHEN should I make an SP and how could I design it in a way that it conforms to the principles of Object Oriented Design (if at all possible).
So far I can see the advantage of an SP, it is quick.
But the disadvantage is that your business logic gets scattered between application and database. And when we would get a client that is not using SQL, but Oracle, we would have to rewrite all of our SP's to still conform to our business logic (while all of our classes in VB or C# can easily be re-used).

So how would I make a well-thought decision between using an SP or using VB or C# classes?

milenalukic 6-Mar-11 5:15am

The advantages of using SP is speed and reducing network traffic. If I get it correctly you have the same front end which you use for multiple customers who could be using different DB types but require the same business logic.

Business logic makes more sense to be on the DB side with only results being transported. SP are best used when there is a lot of calculation to be done and when only a small result set is required of the whole data otherwise transported e.g. Creating invoices, getting customer history.

SP can be easily scripted and implemented on another db with minimal changes.

How to create a Stored procedure:
CREATE PROCEDURE MY_SP

--ADD PARAMETERS IN THE FORMAT
@Customer varchar(50),
@Date datetime

AS
BEGIN
SET NOCOUNT ON;

Select * from customers inner join invoices on customers.CusID=Invoices.CusID
where Customers.name=@customer
and invoices.date>=@date

END
GO

This would give the invoices for that customer on or after that date.

You can put any query in the proc and any calculations required.
To run it all you will need is to call the proc and pass the parameters. It will then return the required result.

The more your data increases the more you would see the benefits as otherwise you would call all the customers and all the invoices and do your calculation on the front end. Server are generally more powerful than client PCs and you must remember that any system will only be as fast as the slowest link.

PS If you require any changes to business logic you can change the procs as required and as long as you do not change the input parameters or the format of the result set you will not need to recompile the front end.

I hope this helps you out.

Sander Rossel 6-Mar-11 11:13am

Thanks again for your answer. I am a bit closer to what I want to know :)
Speed and filtering of data seem pretty good arguments to keep that sort of stuff on DB side. But consider this:
When I want to get a price for product X, customer Y, between dates A and B I have to get various prices from various tables. These tables may contain lots of data while I only want one price. So we are using an SP. This is all good, but consider how the SP would look. We are getting the price from our first table. If some value is null we will get data from another table, if not null then we use a case... Long story short, a single SP can be up to hundreds of lines of code. If ONE thing, and ONLY ONE thing changes then good luck finding it in the SP after a few months...

This is against the principles of OOD, which I am using in my VB code. One of these principles states that a class should have a single responsibility. So now one thing changes and I might have to change a table, which forces me to change the SP, which forces me to change the class that calls the SP etc. Moreover, who wants to work with such big SP's? Last week I had an SP with about 800 lines of code.
So clearly I am doing something wrong in my database, unless it really cannot be done in a proper OOD manner. I was thinking on creating an SP that calls on different SP's and functions, but how well can they be kept together and how maintainable is that?

So when to use an SP seems pretty clear now, but how do you keep the big SP's (I'm talking hundreds of lines of code) easy to understand and maintain?
Do you have some documentation that could help me on this matter?

milenalukic 7-Mar-11 6:28am

You are on the right track. What you will need to do is to split your SP into smaller ones that perform one action and call what is necessary from your main sp depending on your original result.
Using nested SP is common practice.
Use in line documentation to be able to do future changes.

You may also need to consider if your db design is normalised.

Sander Rossel 15-Mar-11 3:18am

Thanks, that's what I wanted to hear! :)
I think with the use of schema's things should become a bit more seperated too.

milenalukic 17-Mar-11 6:17am

Good luck!