If you can easily enter your data - and quickly retrieve useful reports - your business will thrive.
If entering an order is a slow, multi-step operation - and massaging your data into meaningful reports takes hours and hours - your business will suffer.
A database is a complex tool used to store and retrieve information - quickly, efficiently - and safely.
It should have these features:
- Group data in a meaningful format: i.e. show the total unit sales of individual widgets broken down by month.
- Enforce referential integrity. This is a fancy way of saying that if you have invoice items - they can only exist in the database if that invoice also exists.
- Locate records quickly using an index. This allows you to find in an instant all employees that have worked at the company for over 5 years that live in a zip code of 75001.
Consumer databases such as Microsoft Access or dBase (used by many contact managers) cannot compete with the
professional databases such as Microsoft SQL Server.
Record Locking and Accidental Data Loss
Consumer databases directly share a single file over a network connection.
The database itself runs on the client and attempts some limited multi-user support using a shared locks file on the server.
This locking scheme is far from perfect, locking out entire sections of the database and slowing performance, so it is seldom used.
Generally, no real record locking takes place - and one user can easily overwrite another user's changes.
- Professional Databases implement true record locking. There are also more sophisticated methods that allow greater control.
A properly designed database and program interface can guarantee that one user never overwrites another's data by accident.
When a consumer database is used for a web site, it can only handle one request at a time - a single thread. This means that everyone has to wait until that request
has been processed, however long it takes.
A professional database is multi-threaded and allows many simultaneous requests to be processed at the same time. Visitors to the web site will experience
no long delays.
Having recently surpassed Oracle in speed, and easily surpassing Oracle in price/performance and support, we have chosen Microsoft's SQL Server as our primary database.
SQL Server is available in clustered versions that can handle terabytes of data, and several smaller versions designed for small to mid-sized companies.
SQL Server 2008 is also available in a scaled-down version limited to 4GB databases suitable for even the smallest office.
SQL Server is the best choice for an Internet application or a graphical program managing all of your customer data.
As many of our clients also have data in legacy databases such as FoxPro, Access, dBase, Excel - or even flat file text databases, we have become experts in interfacing with these legacy systems.
We can create a web server using SQL 2008 that interfaces with a legacy database - even on another operating system such as Unix.