Quick Guide: Databases, Tables, Columns, and Indexes

The basic model MySQL uses for structuring the data it stores is fairly simple to understandbut, perhaps, only when you already understand it. The basic model is as follows:

• The MySQL server contains one or more data-bases.

• Each database has a name and contains one or more tables.

• Each table has a name, a table definition, and zero or more rows.

• Each table definition specifies one or more columns for the table and zero or more indexes.

• Each column has a name and a defined column type.

• Column types are a predefined part of MySQL. Each one has a name and defines a list of values that columns of the given column type can contain.

• Each row in the table contains one value for every column in the table definition.

• Indexes reduce the effort it takes for the MySQL server to find a specific value in a specific column (or set of columns) of the table.

Tip

Review the "MySQL Table Terminology" illustration in Chapter 1, "Maps of MySQL," to get a better feel for the discussion.

Databases are an easy concept to understand; they are just containers. Tables are a bit more difficult. Here is a simple metaphor to help you understand them.

A Simple Table Metaphor

A paper personal address book (the archetypical little black book) is a good rudimentary model for a database. Your simple version of the little black book contains pages where you can write information about friends or colleagues. Each entry for a person in the book is structured and contains a labeled space for the person's name, email address, and date of birth.

Additionally, each page is marked with an index tab to help you find people who are recorded in your book. Without the index tab, you have to look through the book until you find the person for whom you are looking.

Your book might look something like Figure 2.1.

Figure 2.1. A personal address book.

Conceptually, the book can be thought of as a table of information that is composed of rows and columns.

Each row of the table refers to a single person, while each column in the table contains attributes about the person (such as name, email address, birthday, and so on.)

A tabular representation of the little black book is shown in Figure 2.2.

Figure 2.2. The personal address book as a table.

In your paper address book, you put a person on the page that is marked with the right index tab for the first letter of his last name. Maurice Sendak, for example, is put on the same page as index tab S.

In your table, rows are stored in an arbitrary order. To help you find the right row without having to scan through the database, columns in the table can have indexes. Conceptually, indexes are lists of sorted values from a column that are correlated to the actual position of the value in the column.

The diagram in Figure 2.3 illustrates how an index correlates to the actual position of rows in a table.

Figure 2.3. Actual position of rows in a table.