Learning MySQL and MariaDB

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