What is the difference between Database Abstractio

Question

I am actually stuck in 3-tier structure. I surfed the internet and found two terminologies "Database Abstraction Layer" & "Data Access Layer".

What are the differences between the two?

Answer 1

My understanding is that a data access layer does not actually abstract the database, but rather makes database operations and query building easier.

For example, data access layers usually have APIs very similar to SQL syntax that still require knowledge of the database's structure in order to write:

$Users->select('name,email,datejoined')->where('rank > 0')->limit(10);

Data abstraction layers are usually full blown ORM's (Object-Relational Mappers) that theoretically prevent the need to understand any underlying database structure or have any knowledge of SQL. The syntax might be something like this:

Factory::find('Users', 10)->filter('rank > 0');

And all the objects might be fully populated with all the fields, possibly joined with any parent or child objects if you set it that way.

However, this abstraction comes with a price. I personally find ORM's like doctrine or propel to be unnecessary and inefficient. In most cases a simple data access layer will do fine, with manual SQL for anything that requires special attention, instead of having to destroy your application's performance for some syntactic sugar. This area is a pretty heated debate so I won't go into it anymore.

If you meant database abstraction layer, then it would be something along the lines of PDO, so that your code can be used for a larger number of database vendors. PDO works with MySQL, PostgreSQL, and mysqli among others, I believe.

Answer 2

From Wiki:

Data Access Layer

A data access layer (DAL) in computer software, is a layer of a computer program which provides simplified access to data stored in persistent storage of some kind, such as an entity-relational database.

For example, the DAL might return a reference to an object (in terms of object-oriented programming) complete with its attributes instead of a row of fields from a database table. This allows the client (or user) modules to be created with a higher level of abstraction. This kind of model could be implemented by creating a class of data access methods that directly reference a corresponding set of database stored procedures. Another implementation could potentially retrieve or write records to or from a file system. The DAL hides this complexity of the underlying data store from the external world.

For example, instead of using commands such as insert, delete, and update to access a specific table in a database, a class and a few stored procedures could be created in the database. The procedures would be called from a method inside the class, which would return an object containing the requested values. Or, the insert, delete and update commands could be executed within simple functions like registeruser or loginuser stored within the data access layer.

In short, your basic CRUD functionalities/logics on business objects to push to/pull from Persistance/Storage layer falls here. For most cases you might want just this. ORM mapping, interfaces of business objects of Model etc fall here.

Database Abstraction Layer

A database abstraction layer is an application programming interface which unifies the communication between a computer application and databases such as SQL Server, DB2, MySQL, PostgreSQL, Oracle or SQLite. Traditionally, all database vendors provide their own interface tailored to their products which leaves it to the application programmer to implement code for all database interfaces he or she would like to support. Database abstraction layers reduce the amount of work by providing a consistent API to the developer and hide the database specifics behind this interface as much as possible. There exist many abstraction layers with different interfaces in numerous programming languages.

Basically, its an additional layer of abstraction so that you CRUD against vendor independent interfaces and worry less about implementation details of various database vendors. You will need this only if you would want to support more than one database. ORMs, Micro ORMs, wrappers, generic driver classes, whatever the name is, etc that deals with connection establishment, parameter handling, execution etc fall here. It's just an additional layer just before Persistance/Storage layer. In 3 tier terminology, both these layers fall under one as they are not logically separate.

---

To summarize, DAL is about data, DbAL is about database. DAL defines operations, DbAL operates. DAL sits behind DbAL which is just behind actual Db. DAL calls DbAL. DAL is a good thing to separate business logics (in Model) from CRUD logics, while DbAL is seldom needed (but I love it). DAL is more high level design mapping, DbAL is more low level architecture and implementation. Both separates responsibilities. ORMs are massive structures that does both for you. I'm not sure how you separate them when using ORMs. You need not since ORMs handle all that for you. Ideally, I would anyway have DAL in one project, and DbAL in another which I would simply call Persistence layer since there is no point in separating Db and operations on it.

Answer 3

Data Access Layer= Create, Read, Update, Delete (CRUD) operations specific to your application domain

Data Abstraction Layer= performs generic database operations like connections, commands, parameters insulating you from vendor specific data libraries and providing one high level api for accessing data regardless of whether you use MySQL, Microsoft SQL Server, Oracle, DB2, etc...