MariaDB Crash Course - Ben Forta [5]
In the database world, that file is called a table. A table is a structured file that can store data of a specific type. A table might contain a list of customers, a product catalog, or any other list of information.
* * *
New Term: Table
A structured list of data of a specific type.
* * *
The key here is that the data stored in the table is one type of data or one list. You would never store a list of customers and a list of orders in the same database table. Doing so would make subsequent retrieval and access difficult. Rather, you’d create two tables, one for each list.
Every table in a database has a name that identifies it. That name is always unique—meaning no other table in that database can have the same name.
* * *
Note: Table Names
What makes a table name unique is actually a combination of several things, including the database name and table name. This means that while you cannot use the same table name twice in the same database, you definitely can reuse table names in different databases.
* * *
Tables have characteristics and properties that define how data is stored in them. These include information about what data may be stored, how it is broken up, how individual pieces of information are named, and much more. This set of information that describes a table is known as a schema, and schema are used to describe specific tables within a database, as well as entire databases (and the relationship between tables in them, if any).
* * *
New Term: Schema
Information about database and table layout and properties.
* * *
* * *
Note: Schema or Database?
Occasionally schema is used as a synonym for database (and schemata as a synonym for databases). While unfortunate, it is usually clear from the context which meaning of schema is intended. In this book, schema will refer to the definition given previously.
* * *
Columns and Datatypes
Tables are made up of columns. A column contains a particular piece of information within a table.
* * *
New Term: Column
A single field in a table. All tables are made up of one or more columns.
* * *
The best way to understand this is to envision database tables as grids, somewhat like spreadsheets. Each column in the grid contains a particular piece of information. In a customer table, for example, one column contains the customer number, another contains the customer name, and the address, city, state, and Zip Code are all stored in their own columns.
* * *
Tip: Breaking Up Data
It is important to break data into multiple columns correctly. For example, city, state, and Zip Code should always be separate columns. By breaking these out, it becomes possible to sort or filter data by specific columns (for example, to find all customers in a particular state or in a particular city). If city and state are combined into one column, it would be difficult to sort or filter by state.
* * *
Each column in a database has an associated datatype. A datatype defines what type of data the column can contain. For example, if the column is to contain a number (perhaps the number of items in an order), the datatype would be a numeric datatype. If the column were to contain dates, text, notes, currency amounts, and so on, the appropriate datatype would be used to specify this.
* * *
New Term: Datatype
A type of allowed data. Every table column has an associated datatype that restricts (or allows) specific data in that column.
* * *
Datatypes restrict the type of data that can be stored in a column (for example, preventing the entry of alphabetical characters into a numeric field). Datatypes also help sort data correctly, and play an important role in optimizing disk usage. As such, special attention must be given to picking the right datatype when tables are created.
Rows
Data in a table is stored in rows; each record saved is stored in its own row. Again, envisioning a table as a