A few meanings
Database schema is one of those fuzzy terms with a couple of meansings and interpretations. Depending on context it can mean:
- Data model in database
- Whole of data structures and programs within a database
- Namespace (element of SQL)
1. Data model
One of the meanings of database schema is the arrangement of tables in the database - specific tables, data types, primary and unique keys and foreign key constraints. It tied with specific DBMS and defined in formal language.
2. Database implementation
In broader sense database schema is the entirety of database elements - tables, constraints, views, stored procedures, data types etc.
3. Namespace (element of SQL)
Another meaning of schema is schema is a specific element in most relational database engines used for grouping objects. You can think of schema as a namespace or container that holds tables, views, functions etc. Their names can duplicate across different schemas.
You create schema using following syntax in most databases:
CREATE SCHEMA HumanResources;
This creates new schema and it is prefix of each object within that schema:
Why use schemas
You may want to split database into schemas for follwing reasons:
- Easy to assign permissions to entire schema,
- Use same name for two or more different tables (or other object),
- Group similar objects to reduce complexity.
Support in RDBMS
Virtually all relational databases support schemas, it's part of the SQL standard. But databases differ in function of schemas and whether they support "database" level of hierarchy.
|Oracle||Server => Schema = User||n/a|
|MySQL||Server => Database = Schema||n/a|
|MariaDB||Server => Database = Schema||n/a|
|Teradata||Server => Database = Schema||n/a|
|Firebird||Server => Database = Schema||n/a|
|Interbase||Server => Database = Schema||n/a|
|SQLite||File = Database = Schema||main|
|Databases and schemas|
|SQL Server||Database => Schema||dbo|
|PostgreSQL||Database => Schema||public|
|Azure SQL Database||Database => Schema||dbo|
|IBM Db2||Database => Schema||n/a|
|Amazon Redshift||Database => Schema||public|
|Snowflake||Warehouse => Database => Schema||public|
|SAP/Sybase ASE||Database => Schema||dbo|
|SAP HANA||Database => Schema||n/a|
|Vertica||Database => Schema||public|
|IBM Informix||Database => Schema||n/a|