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:
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.