Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

We're starting a new(ish) project at work that has been handed off to me. A lot of the database sided stuff has been fleshed out, including some stored procedures. One of the stored procedures, for example, handles creation of a new user. All of the data is validated in the stored procedure (for example, password must be at least 8 characters long, must contain numbers, etc) and other things, such as hashing the password, is done in the database as well.

Is it normal/right for everything to be handled in the stored procedure instead of the application itself?

It's nice that any application can use the stored procedure and have the same validation, but the application should have a standard framework/API function that solves the same problem. I also feel like it takes away the data from the application and is going to be harder to maintain/add new features to.

share|improve this question
    
Related. stackoverflow.com/questions/15142/…. –  Evan Plaice Jun 12 '12 at 19:55
    
@EvanPlaice Kind of, but not really. I'm going to be using stored procedures either way for security purposes, I just don't know how much work the stored procedures should be doing. :o) –  Snuffleupagus Jun 12 '12 at 20:00

2 Answers 2

up vote 5 down vote accepted

No, it is not typical that a stored procedure handles all data validation. From my observations, stored procedures should use assertions as a last line of defense against bad data. Like making sure string data doesn't exceed n character length for a particular field. Stuff like the password must meet certain criteria like having at least 1 special character, 1 number, and not have spaces is usually handled in the application layer.The application layer should be doing the major validation work before the data gets to the stored procedure.

share|improve this answer
1  
A good rule of thumb is assuming data goes into the database without error, will the data be stored as expected, and can it be retrieved properly? It isn't the code's responsibility to determine if impossible data should be stored in the DB (for example a date of 0000/00/00), or a record that requires a foreign key to a child table but doesn't include one on insert. It is up to the DB to reject with error data that is invalid. This ensures that no matter the source of the data, only well-formed and correct data will be stored. –  BryanH Jun 12 '12 at 21:03

There is logic that must must live in the database, such as RI and PK uniqueness check.

The good point about extra logic in the data layer, is that creating code in stored procedures simplifies the the client code significantly and may let you forget about a middle tear altogether if scalability is not the primary concern. It could also reduce the trips between the calling code and the database in the 'happy scenarios/cases'. In addition, as you have identified, it allows 'the enterprise' to share the logic. The downside is that, the more code you put in your data layer, the more your application would be tied to the database and its vendor. You will need the skill in both your front end and backhand in your team. If your team is OO centric, they may not enjoy the SP procedural nature. For example, the ease of using objects in LINQ, may have to be substituted for using experience in SQL. I also think that when you have stored procedure you many not be able to scale your application by adding database servers, but I am not sure about this point. If this is a concern, you may need to research it. You may also be interested in this post: arguments-for-against-business-logic-in-stored-procedures

Edit, It may worth mentioning that version control may be more difficult with code distributed in more than 1 environment.

share|improve this answer
    
That middle tier is where I really like to live, so I'm a little bias. Our team only consists of 4 people, two of which (who retain bitching rights about how its done) likely won't touch it too much. Perhaps it's ultimately a preference, though I really lean towards the application handling most of the logic. –  Snuffleupagus Jun 12 '12 at 20:36
    
@Snuffleupagus, you are correct, you must be comfortable with your choice. However, this might mean duplicating (or sharing) the business logic across applications which is not quite easy. –  Emmad Kareem Jun 12 '12 at 20:41
    
I intended to have an overlaying framework that would handle any real business logic. Any place you would need to log in would call the same function, etc. which should (assuming other devs use it as they should) solve that issue. –  Snuffleupagus Jun 12 '12 at 20:44
    
Keeping stored procedures and referential integrity settings (e.g., triggers) in source control is doable. Hopefully one keeps the database schema in the source control; that's where I'd put the rest. It could be a simple sql file that creates all the magic. –  BryanH Jun 12 '12 at 20:58
    
@BryanH, you are correct, the schema must be well kept track of. This is hard to achieve when modeling is not done first. –  Emmad Kareem Jun 12 '12 at 21:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.