To end up with a good ‘relational database’ application, you need to ask: Do I actually need one? If so, how far do you want to take it? By this I mean, is this to be a simple database to store information about only one, or very few, related entities (i.e. a list of people who attended a certain event, and how they came to hear about the event) which are not likely to change; or a permanent and well used application with several different entities (i.e. Staff, Departments, Branches, SalesFigures for various periods/staff members/departments) which are constantly changing. If the forma is the case, a simple ‘Flat File’ database is all that is required. There are several basic database products on the market that will fully service this requirement but all that is needed is a table in MS Word for Windows or a basic Spreadsheet application, such as MS Excel. If the later is the case, then a Relational Database is needed. Although there are many products on the market, they have several common features and all use some form of SQL (Structured Query Language) to build, edit and query the database.
MS Access is one of the most commonly used applications because it ships with MS Office, which is used by almost everyone now. Access is not actually a database but a GUI (Graphic User Interface) for working on databases, and a (albeit quite basic) DBMS (DataBase Management System). Although Access can be used on any database system, it is usually used as a ‘Front End’ GUI to Access SQL Server ‘Back End’ databases. Recent versions of MS Access shipped complete with a cut down version of SQL Server already built in so can perform several more sophisticated tasks than previous Access versions (which shipped with the Jet Database Engine). Unless the user sets Access to connect to a different database, it will automatically build, and seamlessly connect to, the database engine which shipped with the version in use. In summary, the MS Access application presents a GUI for the user to design and link all the relations (otherwise called tables or entities) and enter data and retrieve information as required. Most of the actions performed by the user in the Access application are converted to SQL commands and sent to the chosen database engine. If the built in engine is used, this is all seamless and transparent to the user.
How to design your application
A well designed database is very often the key to a good and efficient software application. Many developers spend dozens of hours working on an application when some basic database revisions might solve all the problems. Therefore it is well worth spending the extra time initially on a good db design. The following guidelines will head you in the right direction and may well be all you ever need (
Structured Systems Analysis And Design Methodology (SSADM)
SSADM is one of the recognised methods of designing a database application. Many developers now use Prince2. You are not likely to do a good job of designing a relational database in Access, or any other design tool, without learning SSADM or OOAD (Object Oriented Analysis and Design). SSADM is quite logical and not terribly difficult. It is the sort of concept most people can relate to without too much difficulty in today’s world, where everything in business is compartmentalised.
Use the Help system
Have a look at the database you are using and press F1 (or select help) and look for topics on relationships. If there is anything there, that will be your best starting point. If not, you may need another application. It could be that you are using a ‘Flat File’ system. These are fine for simple lists but as your system requirements increase in complexity, the need for data, and referential, integrity will also increase. This is when the data model becomes more and more critical. Access 2003 is pretty good and very easy to use. It has finally ditched the Jet...