VMware ESXi and vSphere Cluster Management

Essential Database Terms: Databases, Tables, Keys, and Indexes

Learn essential MySQL and relational database terms, including databases, tables, columns, rows, primary keys, foreign keys, indexes, and relationships.

Before writing SQL, it helps to understand the vocabulary used to describe relational data. This lesson explains how databases, tables, columns, rows, keys, and indexes fit together in MySQL.

What Is a Database?

A database is an organized collection of related data. For example, an online shop might store customer information, product details, and orders in one database.

A database is the collection of information itself. MySQL is a relational database management system (RDBMS): software used to create, store, retrieve, update, and manage databases. The MySQL server or database software runs the database system; it is not the same thing as an individual database stored within that system.

A relational database stores information in tables and represents connections between those tables. MySQL is a relational database management system.

Tables: Organizing One Type of Data

A table organizes one category, or entity type, of data within a database. In a shop database, a customers table stores customers, while an orders table stores orders.

Every table is made up of columns and rows. A database can contain multiple related tables instead of placing every kind of information into one very large table.

Columns and Data Types

A column is a named attribute or field describing one property of the records in a table. For example, a customer table might have these columns:

• customer_id — an identifier for the customer
• name — the customer's name
• email — the customer's email address
• created_at — the date and time the customer was created

Columns have data types, which define the form of values they can hold. Common types include integers, text, dates, and decimal numbers. Columns can also have constraints, which are rules enforced by the database, such as requiring a value, requiring uniqueness, or requiring a valid relationship.

A column name is not the same as a stored value. In a name column, Avery Chen is a value. The word name identifies the category of information.

Rows and Records

A row is one complete stored item in a table. It is also commonly called a record or, in relational theory, a tuple. Each row supplies a value for the table's columns.

In this example, the table has three columns. The first horizontal entry is one row representing Avery Chen, and the second is another row representing Morgan Patel. Columns describe what information is collected; rows contain the information for individual records.

Primary Keys

A primary key is a column, or set of columns, selected to uniquely identify every row in a table. A primary-key value cannot be duplicated, and primary-key columns cannot contain NULL. NULL represents a missing or unknown value.

For the customers table, customer_id can identify each customer even if two customers have the same name. This prevents ambiguity when a record must be selected, updated, deleted, or referenced by another table.

A common design is an auto-incrementing numeric ID. Another option is a naturally unique value, such as an email address, when the application can reliably guarantee that the value is unique and suitable as a stable identifier. Each table has at most one primary-key definition, although that definition may contain multiple columns. A multi-column primary key is called a composite primary key.

Foreign Keys and Relationships

A foreign key is a column or group of columns that references a key, normally the primary key, in another table. Foreign keys represent relationships between tables.

Suppose the customers table is the parent table and orders is the child table:

• customers.customer_id is the customer's primary key.
• orders.order_id is the order's primary key.
• orders.customer_id is a foreign key referencing customers.customer_id.

One customer can have many orders, while each order belongs to one customer. This is a one-to-many relationship.

Referential integrity is the consistency rule that keeps foreign-key references valid. Normally, an order cannot refer to a customer ID that does not exist. If the foreign-key column permits NULL, it may represent an order with no customer reference, depending on the application's rules.

Indexes

An index is an auxiliary data structure managed by the database engine. It helps the engine find matching rows more efficiently for selected lookup, filtering, sorting, or join operations. An index is a lookup aid, not a normal application-data table.

For example, an application may often find customers by email:

CREATE INDEX idx_customers_email ON customers (email);

With a suitable index, MySQL may locate matching email values without examining every row in a large table. The exact execution plan depends on the query, the data, and the available indexes.

Indexes have costs. They consume storage and add work when rows are inserted, updated, or deleted because the index may also need to be changed. A primary key is typically indexed automatically. Other indexes should usually be added for columns frequently used in searches, filters, or joins, rather than indexing every column.

Putting the Concepts Together

Consider a database named shop. It contains tables for different entities:

• The customers table stores customer entities.
• The orders table stores order entities.
• Columns describe attributes such as names, email addresses, identifiers, and totals.
• Rows store individual customers or orders.
• Primary keys uniquely identify rows in each table.
• Foreign keys connect an order to its customer.
• Indexes support efficient access for common searches and joins.

A simplified customer table looks like this:

Here, customer_id, name, and email are column names. The values in each horizontal entry form a row. The customer_id column can be the primary key.

The related orders table could contain order_id, customer_id, and order_total. Its order_id identifies each order, while customer_id connects the order to a row in customers. An index may be useful on customers.email for frequent email searches and on foreign-key columns used often in joins.

SQL Example

The following statements show these concepts in MySQL:

CREATE DATABASE shop;
USE shop;

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_total DECIMAL(10,2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE INDEX idx_customers_email ON customers (email);

CREATE DATABASE creates the database, and USE selects it for subsequent statements. The first CREATE TABLE defines customer columns and a primary key. The second defines orders and a foreign key. The final statement adds an index for email lookups.

Common Problems and Their Causes

Duplicate primary-key value

If an insert attempts to use an identifier already assigned to another row, MySQL can reject it. Primary-key values must uniquely identify records. Generate a new identifier or use the table's configured ID-generation strategy.

Foreign-key insertion failure

An order insert can fail when its customer_id does not exist in customers. Create the parent customer first or use a valid referenced key.

Slow query despite an index

An index does not automatically improve every query. The query may not use the indexed column for filtering or joining, the table may be small enough that a scan is cheaper, or the index may not match the query pattern. Index design should be evaluated against real access patterns.

Confusing a column with a row

Remember the distinction: column names define categories such as email, while a row contains one record's values, such as avery@example.test in that column.

Key Takeaways

• A database is an organized collection of related data managed by database software such as MySQL.
• A table stores one type of entity and is made of columns and rows.
• A column describes an attribute and has a data type and possibly constraints.
• A row, or record, represents one complete item in a table.
• A primary key uniquely and non-nullably identifies each row.
• A foreign key connects records between related tables and helps enforce referential integrity.
• An index can speed up suitable lookups and joins, but it uses storage and adds write overhead.

For the broader vocabulary, return to database terms as you begin learning table creation and SQL queries.