Structure

How are SQL databases structured?

When working with relational databases such as MySQL or MariaDB, data is organized into schemas, which contain tables and views. This structure helps keep data logically separated, readable, and easy to maintain.

Schema

A schema is essentially another word for a database.

A database server (for example MySQL or MariaDB) can host multiple schemas at the same time. Besides your own application schemas, MySQL usually contains system schemas such as mysql, information_schema, performance_schema, and sys. These schemas store metadata, internal configuration, and performance statistics required by the database engine itself.

Most database tools hide or protect these system schemas to prevent accidental modification.

In SQL, schemas are used to namespace tables and views. This allows different schemas to contain tables with the same name without conflict.

Table

A table is always part of a schema. When referencing a table in SQL, you can specify its full path using:

someschema.sometable

A table consists of columns and rows:

• Columns define what kind of data can be stored.
• Rows represent individual records.

Each column has a data type. Common examples include:

• varchar(n) – Variable-length text with a maximum length of n
• char(n) – Fixed-length text of exactly n characters
• nchar(n) / nvarchar(n) – Like char / varchar, but optimized for international characters (Unicode)
• int – Integer number without decimals
• decimal(m, n) – Fixed-point number with m total digits and n digits after the decimal point
• tinyint – Often used as a boolean (1 = true, 0 = false)
• bigint – Large integer stored in 8 bytes (up to ~9.22 trillion)
• timestamp – Stores a timestamp (depending on the database engine)
• date – Stores a calendar date
• datetime – Stores date and time
• …and many more

Column constraints

When defining a table, you can control column behavior using constraints:

Name	Description
UNIQUE	Ensures all values in this column are unique
NOT NULL	Prevents NULL values
DEFAULT	Value used if no explicit value is inserted
AUTO_INCREMENT	Automatically increases the value for each new row (typically for primary keys)
UNSIGNED	Disallows negative numbers

Tables always store data in rows. Example:

Name	Age	Location
John Doe	43	London
Max Mustermann	27	Berlin

Each row represents one dataset with values for every column.

Tip

In many applications, especially when working with APIs or languages like Java, storing time values as BIGINT (Unix timestamps) can simplify serialization and time zone handling. However, DATE and DATETIME are often more readable and useful for reporting and SQL-based calculations. Choose the type that best fits your use case.

View

A view is also a child of a schema, just like a table.

A view behaves like a table when queried with SELECT, returning rows and columns in a defined structure. However, views do not store data themselves. Instead, they are based on a SQL query that reads from one or more tables (or even other views).

Views are commonly used to:

• Simplify complex queries
• Encapsulate business logic
• Provide restricted access to data
• Present data in a more convenient format

Because views are calculated dynamically, their results always reflect the current state of the underlying tables.