Database Reference
In-Depth Information
Creating, altering, and dropping tables
Databases contain tables. Tables are two-dimensional data structures containing
rows and columns. A row corresponds to a single record in a database and records
are divided into columns. Think of database tables like a specialized spreadsheet.
The many multiple tables in a MariaDB database have columns, which can relate in
one way or another. For example, the
id
column in an employee table may relate to
the
employee_id
column in an address table. These relationships (also called
foreign
keys
) are why we call MariaDB a
relational
database.
A database without tables of data is nothing more than a name. Until we create some
tables and start adding data to those tables, our database is empty and useless.
Creating a table
There are few things in MariaDB we will spend more time on, at least in the
beginning than when we do creating or defining the tables for our database.
We use the
CREATE TABLE
command to create tables. Using the command, we
define the structure of the table. The structure includes such things as the number
of columns and the type of data that we want to store in each column. Datatypes
include things such as numbers, text, and dates. For example, if we are creating an
employee table, we might decide to store an employee ID number (number), last
name (text), given names (text), preferred name (text), birthdate (date), and so on.
We might also want to store the e-mail addresses, phone numbers, and home
addresses of the employees, but we don't want to store duplicate data, or define extra
columns that are hardly ever used, so we should put those in separate tables and
then link the records back to the appropriate employee. We do this because people
often have multiple phone numbers and e-mail addresses, and sometimes even extra
home addresses and we might want to store all of them.
The process by which we refine our table definitions is called
normalization
.
There isn't space here for a complete discussion of this process, but the MariaDB
Knowledge Base has a page which discusses it in depth available at
https://
mariadb.com/kb/en/recap-the-relational-model
.
For a basic database for an online store, we might have tables for customers,
products, orders, product reviews, customer addresses, and more. We can create as
many tables as we need, but as mentioned previously, we should give the design
some thought so that we don't store duplicate or unused data. That said, don't
worry too much, we can always make changes after the fact with the
ALTER TABLE
command (see the
Altering a table
section in this chapter).
