Database Structures - Learning MySQL and MariaDB - page 50

Part II. Database Structures
The primary organizational structure in MySQL and MariaDB is the database. Separate
databases are usually created for each separate business or organization, or for individual
departments or projects. The basis by which you might want to create separate databases is
mostly based on your personal preference. It does allow a convenient method of providing
different permissions and privileges to different users or groups of users. However, for a
beginner, one database for one organization is enough on which to learn.
As explained in Starting to Explore Databases , databases contain tables that contain one
row or record for each item of data, and information about that item in columns or fields .
Compared to databases, there are well-established, practical considerations for determining
what separate tables to create. Although some beginners may create one large table within a
database, a table with many columns, it is almost always an inefficient method of handling
data. There is almost never a situation in which it makes sense to have only one table. So
expect to create many small tables and not a few wide tables (a wide table is one with many
columns).
When creating a table, you specify the fields or columns to be created, called the table's
schema . When specifying the columns of a table being created, you may specify various
properties of each column. At a minimum, you must specify the type of column to create:
whether it contains characters or just integers; whether it is to contain date and time in-
formation; or possibly binary data. When first creating a column, you may also specify how
the data to be contained in the column is indexed, if it is to be collated based on particular
alphabets (e.g., Latin letters or Chinese characters), and other factors.
The first chapter of this part, Chapter4 , covers how to create a database — a very simple
task — and how to create a table. I also touch on how to put data into a table and retrieve it,
topics to be greatly expanded in later chapters. Presenting only how to create a table
without showing you how to use it would be a very dry approach. It's better to show you
quickly the point of why you would create a table before moving on to other details related
to tables.
When you first create tables, especially as a beginner, it's difficult to know exactly what to
put in each table's schema. Invariably, you will want to change a table's structure after the
table is created. Thus, in Chapter5 we'll look at how to alter tables after they have been
created. I could have placed the chapter on altering tables after the chapters on manipulat-
ing data, but you would inevitably need to jump ahead to it at some point when you realize
that you created a table incorrectly while experimenting with MySQL.