Components of a database
Tables, Columns and Rows
These three items form the building blocks of a database. They store the data that we want to save in our database.
Columns
Columns are akin to fields, that is, individual items of data that we wish to store. A customer's name, the price of a part, the date of an invoice are all examples of columns. They are also similar to the columns found in spreadsheets (the A, B, C etc along the top).
Rows
Rows are akin to records as they contain data of multiple columns (like the 1,2,3 etc in a spreadsheet). Unlike file records though, it is possible to extract only the columns you want to make up a row of data. Old "records" that computers read forced the computer to read EVERYTHING, even if you only wanted a tiny portion of the record. In databases, a row can be made up of as many or as few columns as you want. This makes reading data much more efficient - you fetch what you want.
Tables
A table is a logical group of columns. For example, you may have a table that stores details of customers' names and addresses. Another table would be used to store details of parts and yet another would be used for supplier's names and addresses.
It is the tables that make up the entire database and it is important that we do not duplicate data at all. Only keys would duplicate (and even then, on some tables - these would be unique).
Keys
Keys are used to relate one table for another. For example. A customer places an order for some parts. We need to store the customer's details, the parts ordered and the supplier of the parts (to ensure we have enough stock or place a new order to restock).
How do we "link" all this information together? How can we separate one customer's order from another? Suppose we have the following tables to store this information.
Customer Details
Customer Order
Parts
Supplier
We allocate a unique customer number on customer details - that is, every customer on customer details has a unique number. When an order is placed, that number is placed on Customer Order. A row will be produced for every order a customer makes. We have "joined" these two tables using a key - Customer Number. Using this key we can find out all the order that customer "1" has made.
On Customer Order we will use another key to identify the parts they have ordered. Each part on the Part table will (like the customer) be unique. On the Part table, a supplier number is used to link each part to a supplier.
On some tables (such as Customer Detail, Part and Supplier) the key is unique - we only have "one" of each. On other tables - Customer Order, we have "many" keys of the same value. This is known as a "One to many" relationship and is very common in databases. That means, one record in Customer Detail relates to many records on Customer Order.
Rather than storing the name and address of the customer on the Customer Order table, the short Customer Number is used instead making things much more efficient (see last week's article on this topic).
There are (usually) two types of keys used in databases. Primary Keys and Foreign Keys. A Primary key is a column (or columns - keys are not restricted to just one column but can be made up or two or more columns), that is used when this table is searched. Primary keys should always be unique and are the main way a table is accessed.
Foreign keys are those keys on a table that are used to join to another table. So on Customer Order; the customer number is a foreign key as it is used to find out which customer placed the order. A table can have more than one foreign key (the "part number" would also be a foreign key).
Forms
Forms are used to enter or look at data. They are the screens that make up the application that allows us to enter and retrieve data. Each "field" on a form usually relates to a column on the database but this is not always the case.
For example, suppose you have a field that shows a total of two or more items on a form. The total would NOT be stored on the database. Why? Because if one of the columns were incorrectly updated, you wouldn't know which was incorrect. For example. Suppose you had 3 columns on a table - A, B and C. A is equal to B + C. Suppose the columns contain the following values:
A = 11
B = 7
C = 5
Which column is wrong? You might think that A is wrong, but perhaps B should be equal to 6 or maybe C should be 4. You just don't know. This is why derived items are not stored on the database but can be shown on a form.
The form should have some form of validation so that the user cannot enter "rubbish" onto the database. Numbers must store numbers; dates should store dates and so on. Depending on the application, there can be some very complex validation.
Reports
It is all very well placing all this data into the database, but if you cannot access it in a format that you want (usually on paper), then it is extremely limited in use. Every database should have some kind of reporting facility and I usually find that the report side of things is what makes a good (or bad) database application/package.
Reports should be easy to produce and be quick. Simple reports should be so easy to produce that anyone can do it. More complex reports will need someone with programming/database knowledge, as it is possible to produce really silly results!
Query
Queries are little programs that let the user ask questions. Things like "Give me a list of all the customers who ordered something last month" or "List all customers in a specific area". Queries can and often are the foundation of reports (in that a report can be based on the results of a query).
Depending on the database, queries can be constructed on screen or you may even be able to type in a query command direct. Queries often use a language called "SQL" (Structured Query Language) which is a computer language that makes accessing databases simple.
These then are the general components of what makes up a database. Next week, we take a simple look at some basic design concepts for your tables.
Till then.
Articles in this Topic
