Deciding database need

I have small .net application which I sell. I was using Access as database 开发者_高级运维but I think it's not flexible and fast enough. Also I'm planning to enable network database access so I need suggestions which database system to move.

My program is a shop sales organizer and I record every sale on a shop. I need customer, stock, sale records in the database. Some clients make 10k sales and about 100k products in a month.

EDIT:

Also the database system must be easy to manage and easy to backup.

If you want zero configuration, I'd recommend the embedded database SQLite. It is , small, efficient, reliable, and free.

You don't have to go through the trouble of setting up a db server on the users machine and as long as you don't have tons of traffic, works well in a networks setup.

I'm not sure how you came to the conclusion that your current configuration using an Access file isn't sufficient. Beware of all the Access horror stories that usually start with, "I haven't used Access since 2.0, but I recall some problem. with, blah, blah, blah." About as bad as judging Windows 7 because of some crash you experienced with Win95.

Seems like a noticable performance problem by your clients with larger datasets would have been recognized or your product is experiencing too many errors that you attribute to the data file. Just seems like there would be other features and functionality that clients would appreciate a lot more over premature scaling.

You mentioned SQL Server CE. It can handle up to 4 gigs of data just like Express. Both can be deployed with your installation package. CE's installation is much smaller: 1.8mb v 197mb. Both offer Transact-SQL, but CE lacks Procedural T-SQL as well as stored proc, views and triggers (A big reason not to use it IMO.). Express is server based and has more service features. It's probably more secure by denying users direct access to the data files and to make things easier offers Role-based security. CE files are document safe since they have no code and you could create your own file extension that is specific to your application. I'm guessing your app runs most of the time on the installed machines so having the over-head of SQL Express service running all the time may or may not be a problem. Retail shops don't seem to use high-performance hardware. You may want something lighter.

No conversion is perfect. Depending on how you've developed your application, sticking with a MS product may be easier. It all depends.

MySQL is a great choice. And I believe newer versions (finally) allow for strictly enforced foreign keys. I have used this on many occasions.

If you will be redistributing your system (so users can set up their own sales shop), I highly recommend sqlite. It's fast, free, and growing in popularity all the time. There are now APIs for all major programming languages. Also, there are ways to run it on a webserver, if that's a consideration for your project.

MySQL is fast, small, efficient, reliable, and free. I'd recommend it.

What makes you feel Access is not flexible or fast enough? Access also works well with multiple users on a network.

Note that there is a difference between the database engine portion of Access and the UI portion of Access which allows you to create queries, forms, reports, nacros and VBA code. Many of the posters here have only mentioned the database engine portion in their reply.

what's not flexible, the screens, reports, procedures?.. How many: concurrent users?.. transactions per second? Don't forget that you can use SQL engine with access vs. jet, and the best of feature of access is the integration with excel, word and other modules. for access features visit: link text http://office.microsoft.com/en-us/access/access-2010-features-and-benefits-HA101809011.aspx