N00b wanna learn more about database programming

Discussion in 'Programming & Software Development' started by xhanatos, Jul 9, 2007.

  1. bugayev

    bugayev Whammy!

    Joined:
    May 15, 2003
    Messages:
    4,093
    Location:
    Melbourne
    Totally agreed about the logical deletion. One of hte applications I enjoy dealing with does a bit of both , marks certain records as deleted and actually deletes others. Wonderful piece of coding!

    Another option for the OP - Oracle XE is also free and worth a look!.
     
  2. OP
    OP
    xhanatos

    xhanatos Member

    Joined:
    Mar 5, 2004
    Messages:
    6,040
    Thanks for all the valuable info! Sorry for not replying earlier. Therea are so many options to consider but as many of you have suggested, I will try to find a DB theory book (hate ebooks, can't read them for long) and get my hands dirty with mySQL.
     
    Last edited: Jul 11, 2007
  3. xsive

    xsive Member

    Joined:
    Jun 29, 2001
    Messages:
    4,343
    Sounds like poor code organisation & sloppy development practices. Stored procedures don't in and of themselves obfuscate code. Also, pushing all code into a class can be equivalently bad practice to pushing everything into the database.

    Any well thought out system architecture needs to consider how the different layers of the workflow fit together to maximise function and improve re-use.
     
  4. hyperstyle

    hyperstyle Member

    Joined:
    May 24, 2003
    Messages:
    1,731
    Location:
    Brisbane
    I personally don't use stored procedures for general database queries. Mainly because a big app could end up with hundreds possibly thousands of stored procedures if used in this manner. This means they become quite difficult to manage.

    I love stored procedures for processes that require lots of database interaction with logic/result interpretation between requests. It's easier and a hell of a lot faster doing such tasks in the database as opposed to dragging the results out into your app processing and talking to the database again.

    It is true that well structured classes can manage most database interaction. The implementation totally depends on the developer but i've been developing my own framework for 3/4 years that includes a db object access layer and sql compiler. The framework almost follows the MVC pattern (db objects/url routing/controllers/models and views) but evolved out of experience building large applications rather than just implementing 'cool' programming paradigms. Plus at the time no such framework existed that had such features (that i was aware of).

    Different tools for different jobs.
     
  5. xsive

    xsive Member

    Joined:
    Jun 29, 2001
    Messages:
    4,343
    Exactly right. A DB service layer.

    How can you go on to contradict yourself immediately? This is simply not true. Having a huge class with lots of processing that's entirely based on what the database returns makes no sense. Push it into a stored procedure and let the database do the work. Your application needs to value-add to the workflow or else it shouldn't exist. Pushing all your business logic into a single layer leads to poorly designed monoliths that are difficult to maintain and scale poorly.

    Finally, we agree again :)
     
  6. GumbyNoTalent

    GumbyNoTalent Member

    Joined:
    Jan 8, 2003
    Messages:
    9,842
    Location:
    Briz Vegas
    Um NO, all business logic should exist in the one layer this the main arguement against Stored Procedures. Also workflow is not a function of the database, especially if it is dependent on business logic, which is is most of the time. This is why we have a middleware layer.
     
  7. mordy

    mordy Member

    Joined:
    Aug 30, 2001
    Messages:
    5,100
    Location:
    melb
    you are mistaken, a stored procedure is part of the data layer before the actual database, its just that in terms of performance doing it in the DBMS which is properly indexed and optimised is orders faster than retrieving all the rows and doing it inside the application database layer.

    Here we arent placing the business logic below the business layer, we are just returning a result-set required by the business layer, quicker than with the db layer.
     
  8. xsive

    xsive Member

    Joined:
    Jun 29, 2001
    Messages:
    4,343
    What on earth are you talking about? You put the business logic where it makes sense. Some business logic should be done at the UI layer (display rules & workflow sequencing for instance), some should be in the middleware (like the actual high-level workflow) and some should be in the database (rules about what combination of values are okay vs not and who can modify the actual data).

    Pushing everything into one layer is horrendously bad practice and leads to inflexbile architectures.
     
  9. hyperstyle

    hyperstyle Member

    Joined:
    May 24, 2003
    Messages:
    1,731
    Location:
    Brisbane
    ?
    You obviously don't understand application development. Go right ahead and create a database intensive, 200 000 line application using stored procedures the way you suggested. Your app will cease progress about half way through as you will be spending all your time updating and maintaining the stored procedures.

    I will give you a bit of a run down on how my applications work. I have a class called db_object that utilises the ADODB abstraction layer. All database communication goes through it. Whether it's directly or extended by another class depends on the circumstance. But basically it makes life easier for selects, inserts, updates and deletes by keeping track of important info.
     
  10. xsive

    xsive Member

    Joined:
    Jun 29, 2001
    Messages:
    4,343
    :rolleyes: I'm not going to get into a e-penis measuring contest with you about who has more experience doing what. I would further suggest that you avoid cheap shots in future posts as they contribute little to the discussion and even less to your credibility.

    As opposed to spending all my time updating and maintaining class files? If all I'm doing is processing work on stuff that lives inside the database there is absolutely no reason to move that junk elsewhere. Infact, there's lots of reasons to keep it where it is (consistency, maintenance, performance to name but a few).

    I completely appreciate the nicety of using a data access class that works at a higher level than the db connection infrastructre (ADODB, ODP, whatever). What I was suggesting was that it's not the best idea in the world to move all database interaction code into your application layer if the nature of that interaction requires lots of processing of result sets without any other value-added functionality added by the application invoking said database.

    It's really not very difficult to grasp -- step back from your code for a second. Map the process your application is supporting and identify the activities or groups of activities that are not heavily interdependent. Those are candidates for discretised services that can be implemented into various application layers. Next, look at the nature of the work -- if you're fetching from a database in order to construct some kind of object from fragments of data across many systems then you definitely don't want that processing in a database. However, if all you're doing is lots of validation type work toward the end of the process (just before you save) that doesn't rely on interacting with other systems then that's probably better implemented in the database.
     
  11. hyperstyle

    hyperstyle Member

    Joined:
    May 24, 2003
    Messages:
    1,731
    Location:
    Brisbane
    Your stance on the issue is the typical db admin perspective. You know databases, you know them well, what they can do and you want to take full advantage of its features. The typical programmer will want to do everything on the application side because they don't know shit about databases. Both groups do what they know best and both aren’t wrong. There is a happy balance though that depends on the applications use and complexity.

    As opposed to further development. If you've created your classes correct there should be too much updating to do. Stored procedures aren't as flexible.

    I agree, perfect use of stored procedures.

    No one said anything about it being difficult to grasp.

    Ok i see the issue here. I come from a web development background (I trust the application but not the users). It appears you look after databases for client side apps (the application basically is the user, so you can't trust it). Hence the difference of opinion regarding the best use for stored procedures. All is forgiven.
     
    Last edited: Jul 12, 2007
  12. Elyzion

    Elyzion Member

    Joined:
    Oct 27, 2004
    Messages:
    7,449
    Location:
    Singapore
    What happens when your working on a large project and you decide "right, i want to hire someone to optimize and maintain the database"...
     
  13. hyperstyle

    hyperstyle Member

    Joined:
    May 24, 2003
    Messages:
    1,731
    Location:
    Brisbane
    Yes. But it's not always in your interest to do everything they say.
     
  14. xsive

    xsive Member

    Joined:
    Jun 29, 2001
    Messages:
    4,343
    Actually, my stance is that of an enterprise developer that has to deal with end-to-end problems across entire corporate architectures.

    So you learn; don't overcomplicate your codebase because you don't know how to make proper use of the available tools. The happy balance you mention lies in knowing how to architect a system that is flexible, extendable, scalable and most importantly maintainable. Putting stuff where it doesn't belong only makes your life harder as the environment around your application (and, consequently, the expectations of it) grow.

    I don't see the difference. If your code is well written it should be just as robust regardless of where you keep it. If your stored procedures tend to be more brittle than your classes then I suggest the interaction between the two layers needs to be thought out better.
     
  15. GumbyNoTalent

    GumbyNoTalent Member

    Joined:
    Jan 8, 2003
    Messages:
    9,842
    Location:
    Briz Vegas
    I have 1 question for Stored Procedure advocates, just one...

    Which has better debugging tools, PL or *insert language of choice* Java?
     
  16. Elyzion

    Elyzion Member

    Joined:
    Oct 27, 2004
    Messages:
    7,449
    Location:
    Singapore
    Why not? They obviously know more about database management than you otherwise you wouldn't be hiring them.

    But having all your queries in your class's now means you have to give them access to your code.

    If it was done in a stored procedure and he saw a paticular query was taking longer to execute than others, he can open it up and there might be something like:

    SELECT * FROM tablename
    WHERE columnid IN (SELECT columnid FROM table WHERE column = condition)

    And hes like "damn, bad query i'll fix it..."

    SELECT * FROM tablename
    WHERE EXISTS (SELECT * FROM table WHERE column = condition)

    It retrieves the same data and takes far less time to execute.

    (it's an example so no need to debate the query)

    *looks worried*

    I don't understand your question...
     
    Last edited: Jul 13, 2007
  17. hyperstyle

    hyperstyle Member

    Joined:
    May 24, 2003
    Messages:
    1,731
    Location:
    Brisbane
    You can't just let anyone run the show. They may know database management better than the project manager and the lead programmer but do they know the applications architecture inside out? Probably not. So you take their advice and get them to implement the parts that are going to work well as a whole.

    Yeah good point.
     
  18. GumbyNoTalent

    GumbyNoTalent Member

    Joined:
    Jan 8, 2003
    Messages:
    9,842
    Location:
    Briz Vegas
    Every tried debugging a complex PL in Oracle (well Oracle from 2-3 years ago), I know I would much rather debug logic structures in a proper environment.

    As for the query analogy, and good DB would check the logs and wouldn't need to see the code, how else would they know.
     
  19. Elyzion

    Elyzion Member

    Joined:
    Oct 27, 2004
    Messages:
    7,449
    Location:
    Singapore
    Oh of course, no point in hiring someone if they can't work as a team. And i should hope they test things before they go changing stuff too.

    Oh, i've only used MySQL 3,4,5 Posgress, and SQLServer 2000/2005.

    I thought thats what i mean by "notice a query running slow" if you were checking logs and such and noticed a paticular query running slow, or a paticular stored procedure.

    I don't think the query shows if your using a stored procedure, just the stored procedure and the parameters. So they would need to open the SP to see whats wrong.
     
    Last edited: Jul 13, 2007
  20. GumbyNoTalent

    GumbyNoTalent Member

    Joined:
    Jan 8, 2003
    Messages:
    9,842
    Location:
    Briz Vegas
    So we agree that SQL is easier to tune when not a SP?

    PS :: PostgreSQL(PostGres) or Progress cause I never heard of Posgress or as I typed earlier Prostgress :)
     
    Last edited: Jul 13, 2007

Share This Page

Advertisement: