|
Should applications, which are for solely reporting purposes, rely on stored procedures or should the logic for the report be done on the app side? For example, if I need:
Employees who have worked on Tuesday at least 5 out of 9 weeks prior to DateX, and also worked day after DateX, and have worked more than 90 days in the past 12 months
If no DBA's are involved and I am the only developer, I can either
1. Write a stored procedure which will return the employee records which fit the above criteria OR
2. The client can get all the Employee records from the Employee table and Hours from the Hours table for the past 12 months and then perform the search for the criteria on the client side
Which option is better? How much business logic should we write on the db side--is there really any limit or convention?
CodingYoshi
Visual Basic is for basic people, C# is for sharp people. Farid Tarin '07
|
|
|
|
|
You won't be limited to client-side searching if you avoid sprocs. Clientside searches can also be initiated from inline SQL.
CodingYoshi wrote: Which option is better? How much business logic should we write on the db side--is there really any limit or convention?
There are several, some of them are being defended religiously. I'd recommend using dOOdads or LINQ.
dOOdads has the advantage that you can use the MyGeneration application to generate the framework and sprocs, and place the business-logic in the predefined BL-layer. For bonus-points, put the BL in it's own DLL.
LINQ has the advantages of simplicity and elegance.
--edit--
CodingYoshi wrote: Employees who have worked on Tuesday at least 5 out of 9 weeks prior to DateX, and also worked day after DateX, and have worked more than 90 days in the past 12 months
These go by preferation in the database; if you decide to write another frontend (web, or a newly purchased control-library) then you'll already have your (by then already tested) BL in place. This also avoids duplication of the code.
You got multiple ways to implement this. From a trigger, using a RaiseError (with a low severity, or it will close the connection) to fire an event back to the UI. You could also use a sproc that returns a message or an error-id; I personally prefer to raise a non-severe error.
Those things that can't be caught in the conceptual model of the database go in the BL-layer of your application. Those are the BL-rules that state to send a twitter-message or shut down a remote workstation.
I'm curious to the other answers that will appear on this question 
I are troll
modified on Wednesday, July 8, 2009 8:57 AM
|
|
|
|
|
Thanks. I understand I can use LINQ but my question is whether to put the logic within the stored procedure or put it in the DAL (using LINQ) or whatever else.
Anyone else?
CodingYoshi
Visual Basic is for basic people, C# is for sharp people. Farid Tarin '07
|
|
|
|
|
I think the answer is, it depends. There are arguments for and against using stored procs.
Arguments for:
(i) A stored proc may well perform better. Dragging lots of data out of a database across a network onto an app server for further processing may well be a slow operation. Moving the logic down into the stored proc means that you are processing the data at source, without the need to transfer it across the network, which can sometimes be a big saving.
(ii) the stored procedure logic can be reused from several different clients. Maybe you have one application written in VB, another in Java, both needing to access this same summary data. If you put all that logic into a stored proc, you only need to write it once.
Arguments against:
(i) SQL is not a procedural language. You can sometimes find yourself struggling with cursors and loops and temporary tables trying to work around the limitations of SQL. In those cases you might be better off doing it in the high level language of your choice (C#, Java, VB, Ruby, Python, whatever) where this sort of complex logic is easier to code. (Some modern databases let you write your stored procs in a language like C# or Java, which changes the landscape somewhat.)
(ii) Some development shops like to minimise the number of languages they have to learn and support. This is particularly true if people are using some sort of data abstraction layer so that programmers are hidden from having to know SQL (LINQ, Hibernate, iBATIS, ActiveRecord, etc). Putting some of the logic into C# (or whatever your language is) and some into SQL means that either one developer has to know both languages, or the work is now split across two developers (who have to coordinate to make sure they are working in line with each other).
I can't tell you which option to use for your application because I don't know enough about your requirements, your development environment or even your personal taste, all of which play a part in the decision.
|
|
|
|
|
I'm prejudiced - need to get that out of the way first. I alway put the logic for a report in the stored proc, this for and I can make TSQL sit up and beg.
Having said that I would not consider this a report, unless the structure of the request is stable which I don't think this one is. This, to me, is more of an analysis issue. I want to get at the database and find these type of people.
We use SSRS and have a base rule NO intelligence in the report other than setting the parameter for the proc to use. Having said that the rule is flexible, any business will have a requirement that just has to be met and then we build a report just for that requirement and the logic could be anywhere that suits the developer.
This type of request from a user would not even make it to the report development stage. We use ActiveQueryBuilder[^] and RadarSoft[^] cubes to give the user access to the data. Both have been wrapped up and get referenced by our apps.
Radar Soft has a fairly steep learning curve but is well worth it. Query builder is just TSQL in the UI. I do not allow a set of key words to be passed in and they only have RO access to the database. these also allow us to implement another rule, no report longer than about 2 pages - the rest is a data dump.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for the answers, it really has helped!
After some research I have gathered the following:
I think if the logic is not too complicated, the developer is comfortable with sql scripting, and has access to work on the db side then sometimes it is better to put the logic in the stored proc. This, just as David has pointed out, saves us the cost of transferring huge amount of data across.
However, if the data is not simply for reporting purposes but it also might be updated, deleted etc. then it is not a good idea. Furthermore, we always have to be aware that if the app is to be installed on mobile device then we can not use the code because some compact database providers do not support sql stored procedures, for example, SQL CE. The other disadvantage is if we want to know why an employee did not qualify (did not work on Tuesday etc.) then things become a little messy. I mean there are still ways to get around it but they are not the best.
CodingYoshi
Artificial Intelligence is no match for Human Stupidity.
|
|
|
|
|
Good day!
I am a student and I am doing a study on Object Relation Mapping tools and how they fit into various software design patterns and approaches. For now I have tried some tools and read about their weaknesses and strengths related to ModelViewController and n-tier design patterns and also have an experience with some projects. But lately I have found out about domain-driven design (which involves also a model-driven design at some point because domain objects together make a domain model). And I got a bit confused how does this all fit together with ORM and what is ORM role in those designs.
I have found this good article about practical example how to use MVC and domain driven design with Zend and PHP:
angryobjects.com/2009/03/30/writing-robust-php-backends-with-zend-framework/comment-page-1/#comment-1201
The author suggests keeping the domain model completely clean and unaware of underlying persistence. Only some ServiceLayer is needed to convert domain objects to/from persistence objects and as I understood this involves also CRUD operations.
But I want to know what happens if we introduce some ORM into this scenario. As I understand, the right way is to create domain model first and only after that think about how this model will be persisted with or without ORM. Let’s say, we have designed some domain model and now want to use ORM tool for easier persistence. ORM tool lets us generate some entity model and it is just logical that we’ll try to match this entity model with our domain model as closely as possible. But then we get some kind of duplication! We have a domain model with business logic in it and we have the same domain entities but with CRUD or various persistence attributes (maybe not visible but inherited from some base-class or interface). Then the question is - how good or bad idea is to merge our domain model with the ORM generated entities? When is it acceptable and allows us to say that we still have a good domain – driven design and when it is not acceptable because of too much “persistence aware” domain? Is it some trade-off thing going on here (clean domain entities versus duplicated entities)?
I found this article:
ibm.com/developerworks/java/library/j-pop1/
where they use some base classes and interfaces to implement common functionality in the domain model. But still the model entities contain some persistence awareness because of that inheritance. How good or bad is it from the point of view of clean domain model?
Also the same problem arises when we try to use Service Oriented Architecture. Our domain entities are not aware about any serialization so we cannot send them through XML straight away. It is so tempting to add serialization attributes in .NET classes to make them serializable. But I guess this again is not good idea because our domain model gets dirty with underlying technology dependent serialization information. Then again – we need to somehow hide this serialization but can we make it inherited? Of course developing it would get much more tricky that simply adding serialization attributes to the properties of the class because, for example, .NET serialization needs Order attribute for each serializable property to keep them in the right order – InfoPath forms do not tolerate any element order changes in XML schema. So we have a choice again – get domain dirty with serialization or create duplicate domain model entities which handle serialization.
This all got me really confused. What is the right use of ORM tools and serialization to avoid unnecessary duplication just to keep our domain model clean and technology independent?
And if we forget for a moment about that ServiceLyer but stick to the classical MVC - then where is the right place for CRUD operations when using ORM? Is it Controller or Model? But if ORM tool generates classes that do not manage persistence inside those classes then what would be the right way:
a) embed CRUD into model classes;
b) create some other classes which do the CRUD (but I gusee then it would be some service layer again)?
Also, if someone would want to develop some imaginary ideal ORM tool for domain-driven design model - I guess it would be some tool which allows us to create domain model and then match it straight with persistence without making the domain model itself aware of any persistence mechanisms at all. Is this even possible? Maybe you can give me some example of any ORM tool which is the most appropriate for domain-driven design?
Thanks.
modified on Sunday, June 28, 2009 3:44 PM
|
|
|
|
|
That's a long story, and I'm no academic. I'll try to formulate an answer, and (I hope) others will correct me where I go wrong
midix wrote: where they use some base classes and interfaces to implement common functionality in the domain model. But still the model entities contain some persistence awareness because of that inheritance.
You can define a three-tier architecture where you keep all your persistence-related stuff in the DAL. The BL, although it inherits from the base-class, can be separate from the DAL - even physically on another machine if needs be. Yes, there will still be a relation between those two.
The things are loosely coupled, instead of intermixed throughout the entire codebase. Loose coupling has the advantage that you can replace it quickly, simply by writing new derived classes (or new base-classes).
You cannot avoid the coupling completely; there's some part in your application that needs to know where to persist, and how.
midix wrote: And if we forget for a moment about that ServiceLyer but stick to the classical MVC - then where is the right place for CRUD operations when using ORM? Is it Controller or Model?
I'd say neither. Your controller could populate the model with information from the BL-classes, which could be derived from the DAL-classes. Even if you don't follow the three-tier architecture; the controller isn't forced to do SQL - but it sure is convenient if you can LINQ there
I are troll
|
|
|
|
|
Hello all!
First off, sorry that it's a bit long but I have been struggling with this for a few days.
I could use some help with object oriented design. My experience is primarily (nearly exclusively) as a procedural programmer, so I'm struggling with the next phase of the OO design process.
I'm now working on an Employee class that will populate based upon the results of an LDAP query. So far things have been going well, but the first phase of the project has each Employee populated by the results of a FindOne() query. The value used to initialize the instance has been an employee id that is guaranteed unique so it hasn't been an issue.
The user has two options to instantiate a new object. Call the constructor with the unique value, or populate the private variables and then query AD.
Employee emp = new Employee(empID);
Employee emp = new Employee();
Employee.EmpID = "12345";
Employee.ADQuery();
For the next phase I'd like to be able to use FindAll() for the LDAP query and not assume that the value passed in will result in only a single return. For example, a user might want to instantiate the object by doing the following:
Employee emp = new Employee();
Employee.FirstName = "Joe";
Employee.ADQuery();
My first thought was, "Fine, build an array" but after reviewing the design I'm not so sure.
My design ideas have been:
Simply create an array of the private instance variables that currently exist.
I don't like this idea mainly because it seems that it breaks the Employee object model that I've created. I figure that if a user requests an Employee object, it should represent a single employee.
Create a static method that will provide another way to instantiate the object. The static method would create multiple instances of the Employee object... essentially an array of objects.
The problems I have with this is I'm not sure of a few things. I have been assuming the user would first instantiate an object and populate the instance variables with the filter criteria. If I want to allow the user to have access to all the results and not break my model, don't I need to somehow return a collection of Employee objects? Then it would seem the user would need to loop through the construct to retrieve the results. I'm not sure that requiring the looping construct to be in the implementation is the best choice. I'd like to keep the user requirements to a minimum. Also, it makes sense to me that there should be a single way of instantiating the group of objects returned.
In the end, I'm not convinced I'm not going to break my model by allowing multiple things to be returned. If I just return a big group of Employee objects, is this too much overhead? How do I do this? I'm just now getting into the concepts of OOD and am struggling with "proper" design. This doesn't seem to addressed in all of my reading. I can find the ubiquitous "Person" class, but where is the "People" implementation? Is my model just flawed and I need to rethink this?
Thanks for any clarification you can provide!
|
|
|
|
|
After further digging, I've realized I may be combining my data layer with my domain layer. These concepts are a bit foreign to me, but I've realized there is a reason for separating these layers in order to avoid the sorts of design problems I'm encountering.
I have my domain layer model of Employee and I'm trying to do the queries for LDAP in the same object. I realize now I need to separate the data access functions from the Employee class.
Now I'll need to look into how the data access functions remain generic enough yet still allow you to populate the objects. Do the data access functions just exist in a "flat layer" or do they exist as objects themselves? I would think the data access methods would need to exist as extensions of the object they are to be used with.
Thanks to those of you who have read through my design issues.
|
|
|
|
|
Mark McArthey wrote: Now I'll need to look into how the data access functions remain generic enough yet still allow you to populate the objects.
There are a number of different approaches, none of them are a silver bullet. You have hit at one of the stickiest problems in software design. Take a look at ORM[^] for one of the big names in this problem space.
|
|
|
|
|
If you have your employee class built then you can do the following:
public EmployeeCollection
{
// You can use any collection but dictionary will help you spot them easier through their id
private Dictionary<string, Employee> _employees;
public EmployeeCollection()
{
this._employees = new Dictionary<string, Employee>();
}
public void Load()
{
// Ask the DAL class to get all the employees
EmployeeCollectionMgr manager = new EmployeeCollection();
this._employees = manager.Retrieve();
// The manager can either grab the records from the database and create employee objects, add
// to the collection and return the collection OR
// the manager can grab all the employee ids from the database and then create an employee object and
// ask the employee object to load itself.
// In the first approach you need one trip to the database but you will do some of Employee's work
// In the second approach you will need 1 trip per employee to the database and one to get all the ids
// but Employee will do all the work
// You can have many other methods here depending on the domain model at hand.
}
}
CodingYoshi
Artificial Intelligence is no match for Human Stupidity.
|
|
|
|
|
I have a very simple question:
If the View is supposed to know the Model (that is, the View has a field of type Model),
why would I need the controller if the view can modify and get responses from the model directly?
|
|
|
|
|
Quake2Player wrote: why would I need the controller if the view can modify and get responses from the model directly?
Maybe you don't, maybe the pattern and everyone that uses it is wrong.
Or
Maybe you need to study Object Oriented Design Principles[^] more to understand the why, and more.
Your choice. 
|
|
|
|
|
You didn't quoted my entire message.
In fact, the question starts with an "if", so you are entering an "If" without checking the condition.
I understand that one wants to decouple (sorry bad english) the VIEW from the MODEL.. but if the VIEW already contains the MODEL (that is, a composition relationship), you already have the coupling there. As I see it, you've already losed the idea of encapsulation.. because another person working with you could type "m_model." and he would see all the public methods that allow him to modify the model directly from there (from the view). So, again, once you've decided to add the model to the view (Model m_model), I don't really get how the controller helps, in fact it makes the view to take a long road for something it could do (GIVING THAT IT CONTAINS THE MODEL) in less steps.
And btw, a pattern is not a good pattern "just because".
|
|
|
|
|
|
Quake2Player wrote: I have a very simple question:
If the View is supposed to know the Model (that is, the View has a field of type Model),
why would I need the controller if the view can modify and get responses from the model directly?
You don't necessarily. Many frameworks combine the controller and view into one, e.g. MFC's Document/View architecture.
|
|
|
|
|
Thanks,
Another question:
What would be better: That the view works only with strings/numbers or that it works with objects from the model, for example
Creating an account:
a vs b:
(a) User press a button for creating an account after filling the form -> The form sends all the data(strings primarily) with an event (CreatingAccountRequest) -> the controller listens this event -> the controller creates an account object -> adds it to the model
(b) User press a button for creating an account after filling the form -> The form creates an account object -> The form sends an account object with an event (CreatingAccountRequest) -> the controller listens this event -> the controller adds it to the model
Viewing an account:
a vs b:
(a) The form uses the Tag property of controls to contain the objects from the model (for example an Account in a ListViewItem' tag)
(b) The form uses the Tag property of contrls to contain an identifier of the object of the model (for example an Account id)
|
|
|
|
|
Quake2Player wrote: (a) User press a button for creating an account after filling the form -> The form sends all the data(strings primarily) with an event (CreatingAccountRequest) -> the controller listens this event -> the controller creates an account object -> adds it to the model
(b) User press a button for creating an account after filling the form -> The form creates an account object -> The form sends an account object with an event (CreatingAccountRequest) -> the controller listens this event -> the controller adds it to the model
I definitely like (a) better than (b) because it decouples the view from the model; the view in the case of (a) is completely ignorant of the model. If the model changes, it's less likely that the view will be affected.
|
|
|
|
|
Thank you,
Any opinion about the second thing, SHOWING/DISPLAYING ?
|
|
|
|
|
Quake2Player wrote: Any opinion about the second thing, SHOWING/DISPLAYING ?
(a) The form uses the Tag property of controls to contain the objects from the model (for example an Account in a ListViewItem' tag)
(b) The form uses the Tag property of contrls to contain an identifier of the object of the model (for example an Account id)
I'd prefer (b) for the same reason. That way the objects stay inside the model where they belong; the view has no direct knowledge of them. If they change structure at some point, it's less likely to affect the view.
This is just my opinion...
The boundaries of each subsystem, in this case the model, view, and controller, should appear as flat as possible to each other. That is to say each subsystem has a nice, flat API that the other subsystems can access. So instead of something like this, for example:
SomeObject obj = model.GetSomeObject(int id);
obj.SetValue(123);
You have this:
model.SetValue(id, value);
In the second example we have no knowledge how the model represents the values inside. All we want to do is set the values or whatever.
|
|
|
|
|
For a few reasons:
1. Separation of Concerns (SOC): The model should be what the name states--just a model. It should not have other logic. Any logic which is outside the domain model of the "model" and is used for controlling should go in the controller.
2. A pattern is to build vocabulary amongst developers. You do not have to implement it this way. And if you don't, then you are no longer using MVC so you can not tell other developers, "I used MVC pattern here but I decided to throw out the controller."
3. There are many occasions wherein designers have decided to put the controller logic within the model, for example, many .NET classes do databinding this way. DataTable sends an event when data is changed, and the DataGridView handles the event.
Your question below about option a and b, I would go with option b and pass the object over. But instead of asking the object for its state using properties, I would do the following:
Lets say I have Employee class
public class Employee
{
public string Name
{get;}
public string LastName
{get;}
public string FullName
{get;}
public int Age
{get;}
// I will have one method as below which will return the state in a structure in one shot
public EmployeeProperties GetState()
{
EmployeeProperties props = new EmployeeProperties();
props.Name = this.Name;
props.LastName = this.LastName;
props.Age = this.Age;
return props;
}
}
public struct EmployeeProperties
{
// The fields for the struct
}
There is no right and wrong way. It is like art, you might have a more elegant solution than anyone has ever had.
CodingYoshi
Visual Basic is for basic people, C# is for sharp people. Farid Tarin '07
|
|
|
|
|
Nice! Didnt thought on that solution..
Instead of having large methods with a lot of parameters... Thank you
|
|
|
|
|
Hi All,
I am planning to develop a client server application . Can anyone let me know the best way of defining the message protocol between client - server.
|
|
|
|
|
There is no best way, it depends entirely on what you need to communicate between the client and server.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
![Badger | [badger,badger,badger,badger...]](https://www.codeproject.com/script/Forums/Images/badger.gif)