What is a database?
By far the majority of serious databases are relational databases. That means the data is stored in tables. On top of this, these tables are related to each other. In these two sentences we have the foundation on which all relational databases are built. The catch is in how these tables are designed and relationships between them established and enforced.
The father of relational database theory, Edgar Codd, was trained in mathematics, chemistry and had a doctorate in computer science. Coming from someone with such a background, not surprisingly, relational database laws are founded on relational algebra. That's why it pays to stick with the laws and stay inside the box of rules. Otherwise your database is not going to work.
What does a database table look like?
The following table is called "persons" and has three columns (Key, Surname and Name) and two rows of data for two persons. Each row is identified by its primary key. This key may consist of a single value, like in the example, or more than one value. A single value is more common. Whatever the case, a primary key may only appear once in a table.
| Key | Surname | Name |
|---|---|---|
| 12131 | Jackson | John |
| 12132 | Brown | Peter |
The following table, called "dependents", is related to the first table in a one-to-many relationship. One row in the first table may refer to any number of rows, from zero to a great many, in the second table. You will notice that the foreign key in the second table is what ties the two tables together. Of course, no entry for a dependent can exist without an entry for a person. That is if relationship rules are enforced. In sloppy database design they more often than not are not enforced. This leads to anomalies like sales made to non-existent customers. If a person is deleted, all his dependents should also be deleted as our primary interest is in the person.
| Key | ForeignKey | Surname | Name | Relationship |
|---|---|---|---|---|
| 234 | 12131 | Jackson | Billy | Son |
| 235 | 12131 | Jackson | Mona | Wife |
| 236 | 12131 | Black | William | Nephew |
| 237 | 12132 | Brown | Jim | Son |
Although this barely begins to scracth the surface of relational database design, it gives you an idea of how things work. I won't say anything about normalising tables. Just know that tables should be at least in the third normal form for your database to work.
How do I get data in and out of these tables?
That's the million dollar question. Everything explained so far has to do with the database itself; also called the back-end. One can interact with it on the command line (typing in commands in text form) using Standard Querying Language - sql for short. Even though sql isn't difficult, it is way beyond most data entry personnel. What they need is a nice GUI (graphical user interface) in which they can type in values in boxes and push buttons with the mouse. This is often called a front-end. Note that the front-end is NOT the database. It is like a telephone handset used to talk to someone. The handset is not the person one talks to. There are two basic approaches to a front-end: i) an often proprietary front-end that comes with the database software and is tailor-made to connect to the database server, like in the case with Microsoft Access. Each computer accessing the back-end needs a copy of such a front-end. ii) a front-end that lives on a server and is accessed using a browser.
What are the advantages and disadvantages of each approach?
In the case of Access the front-end is tailor-made for either Access of MS SQL Server. It can be quicker to develop databases ready for the end-user this way. Reports are also done using this front-end. Eventually reports are designed to be printed, so using this front-end they are formatted for printing. The disadvantages include first and foremost cost. Each machine connected to the machine running the back-end needs its own copy of the front-end. For that to work each machine needs to have MS Office Professional installed. That comes at about AUD400.00 for the OEM version and about double that for the boxed set. Furthermore, any changes involving the front-end need to be made to all machines. This mostly only involves a central copy of the front-end which is changed and then copied to the client machines. However, it needs to be done.
Front-ends accessed using a browser are often not as pretty as proprietary front-ends. And for many it starts and ends with looks. However, with some of the newer development frameworks the looks are out of the box for forms and not at all bad. Development time can also be slower. Again, with some development frameworks like Ruby on Rails and Grails (my favourite) this can be just as fast as using Access. To overcome the bad way web pages print out I have a style sheet for printing which is used to generate pdf pages on the fly. Of course, pdf is designed for printing, so your report prints well. One needs no specific software on the client machines, other than any web browser, like Firefox, Safari, Opera or IE. This means you don't have to spend a single cent for proprietary software for the client machines. In the case of changes to either the back-end or front-end, all changes made are done to what runs on the server. Therefore there are no changes needed to the client machines. All the software needed, except of course for the custom application written for you alone, can be free. Don't think that free means inferior. Google uses MySQL, very close to 90% of the top 500 super computers in the world run on Linux.
Why a database?
Let's face it, if it was only to store data one could put papers in a box and lock it away somewhere. The beauty of a database is the nearly infinite number of ways one's data can be viewed. For example, one can see who bought two or more of a certain article at certain stores between certain dates and paid by credit card. Of course, the data must be in the database to start off with and one must know how to write the query to display what one wants to see. Mostly people want to see certain reports at certain times. The database developer writes the queries for these and creates the reports. The user fills in things like dates and/or selects values from a drop-down box (html select input) and clicks a button to display the report. If new queries need to be written either one learns how to do it oneself or pays someone to do it, like with most things in life. A database is what makes your data work for you. Believe it or not, for anyone selling anything there is money in data.
Where to now?
You're already at the right place. Now you know what's involved. The next thing is to decide in fine detail exactly what you want - what data you want to store and which reports you would like to see. Then contact Database Applications and let's get going. We can work for anyone anywhere in the world. The Intenet makes that possible.