MySQL – MariaDB – II. Database Structures

How to Change the server name on Windows Server 2019

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 information; 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, Chapter 4, 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 Chapter 5 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 manipulating 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.

Comments are closed.