Database responsibility

I'm starting with Databases. I've been playing around with MySQL and Informix, but never had a real life project.

What is the real responsibility of a Database? Should we add Store procedures and functions to de Database or just let it to be a d开发者_如何学Pythonata repository with no logic?

---

What is the real responsibility of a Database?

A database at its core is a system to store and retrieve data. A CSV file on disk + suitable tools (e.g. Excel) is a simple example of this. In addition, a database might provide additional capabilities, such as transaction control, data integrity, and security.

Should we add Store procedures and functions to de Database or just let it to be a data repository with no logic?

What do you want from the database? If all you want is a "bit bucket", then by all means, store it in a plain file on disk and call it "the database". If you want a bit more than that, use a product that suits your needs. If you want to be able to query it using a 4GL like SQL, use MySQL. If you want transaction control, security, advanced query features, etc etc, use another DBMS if appropriate. Whatever product you choose, however, take advantage of that product. Otherwise you're wasting your time and money. Sure, you'll never use all of the features (only a subset will be useful to you), but if you use very few of them, you may as well downgrade to a simpler product.

If you're using Oracle, you can store procedures and functions (even better, whole packages) right there in the database alongside the data. The real question is, what do you need to write in those procedures and functions - business logic or presentation logic?

Personally, I usually prefer to keep business logic close to the data, whereas presentation logic is custom-made for each interface.

It is possible to create an API layer over your data so that no matter how your applications access your database, they will get a consistent view of it, and they will all modify it using a consistent mechanism. In other words, instead of writing the business logic multiple times (once for each interface), you write it once and once only, then re-use it everywhere.

There are two reasons I've heard why business logic should not be stored in the database:

1. Maintainability: it's hard to change. I never really understood this one. How hard is it to type CREATE OR REPLACE PACKAGE? I suspect it's just the burden of having to learn "yet another language".

2. Database independence: what works in Oracle won't work elsewhere. This is a biggie, and better minds than I have written about this one. Basically, if you really need it to be "database agnostic", you won't be able to use any of the advanced features of the database you bought, so you may as well just use the simplest/cheapest one you can find; in which case, you don't need it to work on every database anyway!

---

Generally it's considered good practice to not place business logic in your database. The main reason is maintainability. It is ok to use stored procedures still, but including business logic within those stored procedures makes your application harder to debug and update.

Including business logic in your database will also effectively tie you to using that one DBMS, and not allow the data layer to remain independent from your application. For example, you may encounter performance and scalability problems with one DB once your application is live, but due to business logic scattered throughout the db, migrating to a more scalable database will be time consuming at best.

If business logic is kept in application code (eg java or c#) and the data layer is abstracted using a data abstraction layer, and an ORM if language permits, then interchanging databases is much less problematic.

We should be striving for separation of concerns, and keeping business logic out of the db helps achieve that.

edit: There are also performance concerns which may dictate that stored procedures are a good place to keep business logic. Containing logic within the data tier (ie the sproc) in some cases reduces the many round trips between the data abstraction layer and the database, which can give a performance boost. I've worked on systems like this in the past, for this reason, but I've always found then difficult to maintain. The problem being that you can look through the classes and procedures to see the business logic and think that's it and you will not see how a particular bug or process can be occurring, then you'll find the stored procedure and see the other half of the business operation (a real pain when the sproc is a 1000 lines!)

As with many things, where you place your business logic depends on the particular problem you're trying to solve.

---

We have a lot of data around us which can be of great use to us. Ordered collection of information helps businesses to take more proper decisions. Databases are ordered storage of information.

Responsibility: In a common scenario, we can state that there is a lot of information around, ordered collection of information is called data, this information relates to an entity, and ordered collection of data is a database, information relating to a group of entities. Collection of these databases is a DBMS. Responsibility of the database is organizing information.

Stored procedures, functions are more like the business processes that you require in order to collect the data you desire to.

First starting point,

Begin: Select database in {postgreSQL, MySQL, SQL Server(Express edition)} and install it. Learn about Codd Rules, Normal forms, Good resource Start learning SQL, write queries. Understand the basics involved in schema creation. Learn procedural language implementation in database. Ask doubts in SO.