Chapter 3: Working with Database Tables

Database tables represent a two-dimensional view of some data. I like to think of a database table as a spreadsheet page-you have rows and columns. The columns make up the database-table definition, and the rows make up each record in the table.

We'll be covering the three basic table operations in this short chapter: creating a table, modifying a table, and deleting a table. These tasks are all quite simple, but they do warrant discussion because you can't work with a database if you don't have tables.

What Is a Table?

As I mentioned previously, a database table is a two-dimensional view of a portion of database data.

Figure 3-1 is an example of what a table would look like if it were placed into a spreadsheet. The columns headings would be the table field names and a row of data in the spreadsheet would be a database record.

Figure 3-1: Table example

For smaller applications, having data stored in a single table is quite common. There is a problem with this, however, when the database starts to get larger-redundant data. For database applications that have more than a few fields where there could be redundant data, it is wiser to store the data in several different tables, each of which holds a specific set of information. Breaking the data down into tables to minimize redundant data is called normalization-a topic we cover briefly at the beginning of this book.

Consider a table of products that contains the product name, part number, price, vendor name, and vendor description. Even a table this small can contain a lot of redundant data! Let's say that this table has 100 products, with 10 different vendors. If we had the data all in one table, we would have the same vendor information in several records, an average of 10 times for each vendor! This is not efficient at all. A much more sensible approach is to split the vendor information into a different table and just have a vendor id number stored in the products table-a relation between the product and the vendor. Figure 3-2 shows a very small table that contains redundant vendor information.

Figure 3-2: Table example with redundant data

Figure 3-3 shows that by using one table for the products and one for the vendors, you can eliminate any redundant vendor data.

Figure 3-3: Table example with two tables

Granted, the two examples above are very small but if you consider similar tables with more records, you imagine how much redundant data is reduced by using multiple tables. So, in simple terms, a relational database is a collection of tables that are related to one another.

This chapter doesn't have a lot of code examples. When you are working with tables, typically you do so from your database administration command-line or the databases' own GUI - so it is not done programmatically.