Oct 14th, 2005, 11:22 PM
Database - Which database system should I use?
There are many different database systems available, and which one you should use depends on many factors. As such, there is no way of saying "this database system is the best", only which is likely to be most appropriate for your particular set of circumstances.
Here's a small list of database systems that are generally available:
- Microsoft Access
- Microsoft SQL Server
- Microsoft MSDE (a limited version of SQL Server)
And some other items which aren't actually databases, but can be used as if they are:
Some of the factors affecting which of these are appropriate to your situation are:
There is a wide variety of prices for the products listed above, ranging from free (such as MSDE/SQL Server Express, MySQL, and Text Files), to amounts which are very high compared to most other software that you would buy (SQL Server and Oracle).
There are obviously reasons why some are more expensive than others, as a general rule the more expensive ones have many more features that you can utilise, such as the ability to handle replication of data between servers, or to store huge amounts of data (Terabytes) reliably and query from it rapidly.
Note that some of these systems need to be installed on a server rather than on a desktop computer, and that there are special (cheap!) Developer versions of some of these which can be installed on desktops.
Of course if you already have a particular system available to you, the cost improves!
Number of users:
Some systems can handle hundreds of users at a time (Oracle, SQL Server, etc), but others can only handle one (Text files, Excel).
MSDE is designed to slow down after a few users (5/8?), and Access has issues with data corruption if more than a handful of users are working on it at the same time.
Some of the systems above will perform badly when the amount of data passes particular limits. What the limits are depend not only on the database system, but also the version.
The ones above with the more noticable limits are:
Excel (64k rows per 'table')
Text files (depends, on how it is opened etc.)
Access (a maximum of a few Gigabytes, and can have issues with data corruption after a few hundred MB).
Some of the above have very poor security (especially Text files which anybody can open in Notepad!), and others have very good security if the options have been set correctly. Unfortunately this can be quite complex!
This sounds odd, but is useful as you can get support easily from other people in the forums! We have several regular members here who are good at answering questions about the following products:
Access, SQL Server, MSDE, MySQL
..and a few who use these:
Oracle, FoxPro, dBase
If you are sending a program and database to someone, you want to be able to easily move files around. For server based systems this is either not possible, or very awkward (the user must have a server!).
In terms of the databases listed above, Access is probably the easiest 'real' database to use in this way, as it is supported by all versions of Windows without having to install anything (if your mdb file is Access 95 format - for others you are likely to need to install MDAC). Access is not required on the target PC, and there are no licencing costs involved.
Other issues include:
Reliability (likelyhood of crashing)
Availability of support from the supplier
Availabilty of consultants (if you might need them!)
Compatability with other software
There have been many discussions in the forums on this topic before, here is a good one: Choosing the Right Microsoft Database
Here is an official comparison of features provided by different versions of SQL Server 2000 (including MSDE).
And here is a similar page for SQL Server 2005 (including Express).
This may have seemed to not helped a great deal, but you should at least know now a few points to be looking for, and hopefully have a shortlist of systems that you may want to use.
If you need any more help deciding, feel free to ask in the Database Development forum (as replies here will not be visible), providing as much info as you can that you think is relevant. I'm sure some members will be able to help you choose
Last edited by si_the_geek; Nov 28th, 2006 at 01:08 PM.
Reason: added SQL Server Express to list of Free systems
Click Here to Expand Forum to Full Width
Survey posted by VBForums.