Database Design and Web Application Development

In most people's minds the word database translates to MS Access. Access is by far not the only database product out there and is very often not the best choice.

Everyone considering using a database should at least know the following for these reasons:

• to ensure you get the best value for money
• to know the minimum of terms to discuss your needs intelligently
• to make the right choices among available products and ways you can interact with your database

Functionally, a database and the application used to access it consists of the following parts:

• the database server which stores the data in tables designed according to very specific principles
• the view layer in which the user views the data mentioned above
  • these views can be forms used for input, editing and deleting of data elements
  • or they can be reports just to view data in some way but not interact with it - see the last point of the first list on the home page
• a code layer that fits between the above two layers that transmits requests from the view layer to the database server, gets results back, maybe does calculations on this data and presents the view layer with a response as feedback

It is important to note that the forms the user uses to interact with the database is not the database, just like a telephone one uses to speak to someone is not that person. Neither is a form a table in the database. A form can show only parts of one or more tables, one table, many tables or any combination of the aforementioned. If one loads a form and someone else changes data in the underlying database the form can even be out of sync with the underlying database, just like a browser which has to be refreshed to show the latest form of the web site. Changing data on a form does not change data in a database until the changed data on the form has been written to the database. This can happen automatically in response to certain events, like leaving the field in which the data was changed, or when a button or link saying something like "update" has been clicked.

The view layer can be part of the database software, like with MS Access, or it can be a web browser, like Firefox, Google Chrome, Safari or MS Internet Explorer.

The foundation of the database application is the table design: get that wrong and no matter what you do, your data won't represent reality and will be unreliable.

The code or computation layer is invisible to the end user. The creator of the database application is responsible for that.

Microsoft Products

MS Access is the database product whose name is most widely known among general computer users. It's intention was to be a home and small business database. It was never intended for heavy use and large data sets. For that Microsoft has MS SQL Server. Much attention was paid to the appearance of forms and reports in MS Access and graphical user interfaces with which people with very little database knowledge could get something going. A significant percentage of work done by Database Applications is on such end user cobbled together Access databases that did not do what the owner had in mind.

Access is perfectly suitable for a light use by few concurrent users database with not too much data in it. The draw-back is that every user who uses Access needs to have it installed on his computer and that costs money. There are also different versions of Access with in some ways limited compatibility between them.

MS SQL Server is Microsoft's industrial strength database product. Note that this is only a server and does not come with a front-end like Access. One can use Access as a front-end or use a browser as a front-end. In the last case one will have to create a middle layer to form the connection between the database server and the browser, like with all database server products. MS SQL Server is intended to run on one of Microsoft's server operating systems. One gets client access licenses for the number of users connecting to a MS server operating system. They are often sold in lots of five. The base server operating system comes with a license for five users. The total cost of both the MS SQL Server software plus the server operating system and any licenses, if needed, is not insignificant.

Open Source Solutions

Of these I'll name only two: MySQL and PostgreSQL. Both are database servers, much like MS SQL Server. Both are industrial strength database servers capable of huge data sets, many concurrent users and heavy use. Much of the Internet runs on them, especially MySQL. These are both available for free and they both run on several operating systems, including Windows. On the Internet they run mostly on Linux, which is also available for free. One can have a Linux server running one of these in a Windows network and connect to it using a browser. After all, on the Internet Windows users connect to Linux machines and have no problems doing so. The choice of what to use, in certain conditions, is a no-brainer.