what is best flexibility and why? Using db views, db tables, stored proc. and objects in tables...

Question

Hi! I want to know what is best practice for using db views, db tables, stored proc. and objects in tables... Which of these is more flexible and why, can You explain?

Best regards!

Answer 1

Each tool has its uses. Your choices will depend on the nature of the application, and its security, performance, and agility requirements.

Nowadays many programmers use Data Access Layers (DALs) for this sort of thing. Many DALs allow you to specify views and stored procedures to call. But you can also run queries against the tables directly, without the need for stored procedures or views.

Unless you are using an object database, you will be dealing with tables rather than objects. Most applications nowadays use table-based database systems, because they are so common, and you can use DALs to manage the object-relational impedance mismatch.

Stored procedures are used when high-performance is needed, and programmatic things need to be accomplished on the database itself (the addition of a timestamp value perhaps, or the addition/subtraction of child records). A good DAL will provide high performance without necessarily requiring the use of stored procedures.

Views are used to manage the interface between the database and the consumer of the data. In particular, the data can be filtered for security purposes. In large database scenarios, a DBA designs and creates the tables, and manages the views and stored procedures that the user is allows to use to access the data.

If you are looking for ultimate flexibility, most of what you need to do can be accomplished in the DAL, without the need for views or stored procedures. But again, it depends on what your application's requirements are. I would say that the larger your application and user base is, the more likely you are to use views and stored procedures in your application.

Answer 2

I would say that, for the most part, stored procedures are a relic of the '90s:

• completely database-dependent
• bad language choice for general purpose programming, regardless if it's plpgsql, t-sql or something else
• hard to debug
• low code scalability, a problem shared with any procedural programming language
• code versioning issues

That's not to say that they (like triggers, views and rules) don't have anything to give: large scale reporting and data aggregation is one example of tasks at which they handle fairly well. For the rest, logic is better placed in the business logic layer (a service, domain entities...whatever) where a variety of tools and more advanced programming paradigms are available.

Ditto for views and triggers.

In e.g. a Java environment, JPA does much better 90+% of the time:

• learn one query language and apply it to any database
• the business logic is more focused, in one place in the application, the BLL
• the code is easier to read and write and it's easier to find people who understand it
• it's possible to express logic spanning multiple databases in a single unit of code

...and the list goes on.