Databasics
This section of the site is for non-specialists who need to know enough to discuss their needs with database developers.
Tables and Relationships
Early in the design process, decisions will be made about how your data will grouped in different categories, or Tables. This stage of Normalisation is what your database developer will do for you, but its useful for you to know a bit about the process as it will assist your understanding in discussions with the developer. It will also help you produce your specification of requirements.
Let's take an example. Imagine you are using a manual card index system to catalogue a book collection. You might begin by recording the following information on each card:
Of course, you may have several books from the same publisher, and by the time you find yourself writing out Bagley & Hogget’s address for the tenth time, you realize there must be a better way! Apart from the drudgery, duplicating information in this way is prone to error. Also, if in the future Bagley & Hogget close their Harlow office and move to Oxford, you will be faced with the task of updating every occurrence of their address.
What you need to do is keep a separate set of cards for publisher information, leaving only the Publisher name on the Books cards. The Publishers card might look like this:
Now its much easier to add new books or new publishers and to update the existing information.
In database terms, this simple system would be said to have two Tables, a Books Table and a Publishers Table. Because each publisher publishes more than one book, there is a One-to-Many relationship between the Publishers Table and the Books Table.
In a real database, there are likely to be tens or hundreds of tables, with many relationships linking them all together. That is the essence of Relational databases.
Queries
Because tables can be related in this way, a database application is able to retrieve information which will sometimes include data from many different tables.
In our simple example, you may want one screen (or Form) in your database to show details of one or more books. This form will using fields available just from the Books table (we say it is bound to the Books table).
You might also need a form showing details of publishers, and this form will be bound to the Publishers table.
Of course, if you want another form to show details of books and the publisher's email address for each one, it will have to retrieve some information from both tables. In this case, the form will not be bound to a table at all. Instead, a query will have been created by the database developer and the form will be bound to the query.
So a query isn't just a way for the user to search the database. Very often, a query is a kind of virtual table, combining and filtering data from different related tables as needed. The result can then be used within the application just as though it were a normal data table. Queries are fundamental to the way in which a database application functions. They are often created using Structured Query Language, or SQL (not to be confused with SQLserver or MySQL which are not languages but server applications).