Database Tables

Databases store data in tables, which define rules for each column such as the data type or length.

For example, here is an employee table:

The ID field is sequential, each record added to the database should get an ID number assigned automatically.

First name, Last name, and Email contain text.

Data Types

Declaring a column's field type tells the database how to store the data. Common types are text, integers, floats/decimals, boolean, and blob.

Each database provider has a different set of data types, so be sure to look them up for your project.

MariaDB: https://mariadb.com/kb/en/data-types/

Mongo: https://www.mongodb.com/docs/mongodb-shell/reference/data-types/

Postgres: https://www.postgresql.org/docs/current/datatype.html

Sqlite: https://www.sqlite.org/datatype3.html

Primary/Foreign Keys

A primary key uniquely represents a record. ID is the primary key for the employee table. A foreign key references a primary key in another table, to use in joins when a query is run.

Here is a payroll table that references employees by ID.

This table has it's own sequential ID for the records, and references the employee ID from the employees table.