General Database Architecture Advise


I have an application that currently uses a proprietary binary file to store transactional data.  In theory it works fine.  The only problem is that it requires the entire set of data to be loaded into memory when the user opens the database.  Additionally it makes it hard if not impossible to allow more than on user to use the data at the same time.

For these reasons and general robustness I would like to switch over to using some type of a prebuilt database solution.  I have been trying to read up about using ADO.NET, microsoft Jet and others.  I am kind of just brand new to all of this and wanted to get some leads on what I need to do to develop a database to store this data in.  I am mostly looking initially at just determining which database I should use.

Can you give me a rundown of my options?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

david_johnsAuthor Commented:
I should add - the application is written in MFC.  I am trying to find a solution that would not require the purchase of a databasing application like Orical, etc...  I have looked into my SQL a little.  Does anyone have experience with using it with a C++ application?
>>>> the application is written in MFC

In MFC there are ODBC classes CDatabase and CRecordset. With these you may use any RDBMS that has an ODBC interface which are - all. The proprietary RDBMS access of a specific RDMS normally is a little faster than ODBC though it depends on the data you have.  

Regards, Alex
david_johnsAuthor Commented:

You recomend using the CDatabase and CRecordSet classes, then?  I have been looking at ADO.NET.  Is there a real avantage to go there?  I also looked at the CDAODatabase and CDAORecordSet classes.  Would these be better?

Then as far as the database itself goes - any thoughts on whether to use:
- Microsoft Jet
- my SQL
- MS SQL Server Express

>>>> ADO.NET. Is there a real avantage to go there?

ADO is the COM like variant of MS database access. As far as I know it supports SQLServer and MSAccess only. I personally don't like COM interfaces (and used other RDBMS) so I never would recommend ADO.

ODBC is the programming interface while MS Jet is the Access driver. With MFC you have 2 rather similar access classes: CDatabase and CRecordset what implements ODBC, or CDaoDatabase and CDaoRecordset what has a similar class interface (I guess 80 percent is equal) but some more features and it is told to be faster (where I have doubts). Only with ODBC you can switch between all RDBMS. DAO is limited to MS RDBMS.

>>>> my SQL

mySQL can be accessed with ODBC or with it's own sql library (for Windows). Same situation: the interface is proprietary to mySQL but has more features and is most likely faster.

>>>> MS SQL Server Express

Never used it and never heard of anybody who used it. Don't think it is much different than ADO.

>>>> You recomend using the CDatabase and CRecordSet classes, then?

Yes, cause it is the easiest way if you come from a propietary file-based database. Performance issues mostly can be solved by defining proper indexes or move the RDBMS to a fast server machine. The CRecordset class is an easy to use MFC class with simple concepts and a cillion of samples. But dependent on your data requirements or your plans for a new RDBMS other solutions can be senseful as well.

Regards, Alex

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.