Thanks for your reply
Seems a lot of works writing the DAL. Are there tools/designers/code generaters available in which the SQL code which interfaces with the database is generated? (A tool which translates object interface -> SQL queries which run on multiple databases). Solutions which works in .NET of JAVA are preferred.

Please send me your suggestions, or link where i can find more information.

it may be smarter instead to use an object orientated appraoch and abstract the data to a dedicated class that is appropriate for that database engine. then you dont need to handle detailed implementation issues untill you have a requiremnt to use a specific db. However you do need to know the design implications of your choices. Ie it would be silly to design somehting which works in Brand A,B or C db if your user/customer insists on using Brand X db

Effectively you extend the parent class to handle the db specific code

There is another solution that requires no changes to the database structure, but it requires a bit more memory and some additional "fiddling" when you do an update or delete.

You can implement "extremely optimistic" locking by making sure that the DAL keeps two buffers with all of the PK (Primary Key) values and any other columns that are "interesting" to it. One buffer is "private" and used only by the DAL, there other buffer is "public" (or at least it can be exposed outside the DAL).

When an update needs to be done, the DAL can check to ensure that there are some differences between the public and private buffers (to be sure an updated is actually needed), then read a check buffer worth of data to see if the attributes have been changed between the private copy and the check buffer. No change in values between the private and the check buffer means that the update is safe. A change means the update will over write another change, which is usually bad (but sometimes you want to force it anyway).

Anyhow, I think I've had enough sleep to make this description lucid, but feel free to abuse me if the idea interests you but I haven't made it clear enough.

-PatP