# Database Schemas A **schema** is a named collection of database objects — tables, views, indexes, sequences, stored procedures, etc. It acts as a namespace: two tables can share the same name if they live in different schemas. ## What a Schema Contains - Tables and views - Indexes and sequences - Stored procedures and functions - Triggers, types, synonyms, database links ## How Schemas Work Across Databases The relationship between databases, schemas, and users varies significantly: ### PostgreSQL A database contains multiple **schemas**. The default schema is `public`. Schemas are pure namespaces — independent of users. ```sql CREATE SCHEMA analytics; CREATE TABLE analytics.events (id serial, name text); -- Access without qualifying: set search_path SET search_path TO analytics, public; ``` One database, many schemas. Cross-schema queries work. Cross-database queries don't (without `dblink` or `postgres_fdw`). ### MySQL MySQL treats **schema** and **database** as synonyms — `CREATE SCHEMA` and `CREATE DATABASE` do the same thing. There's no sub-namespace within a database. ```sql CREATE DATABASE analytics; -- same as CREATE SCHEMA analytics USE analytics; ``` ### Oracle In Oracle, **user = schema**. `CREATE USER` automatically creates a schema with the same name. There is no way to create a schema without a user. ```sql CREATE USER analytics IDENTIFIED BY password; GRANT CONNECT, RESOURCE TO analytics; -- The schema "analytics" now exists, owned by user "analytics" ``` `CREATE SCHEMA` in Oracle doesn't actually create a schema — it's a convenience statement for creating multiple objects and grants in a single transaction. ### Microsoft SQL Server Schemas are explicit namespaces, decoupled from users. A user can own multiple schemas, and a schema can contain objects owned by different users. Default schema is `dbo`. ```sql CREATE SCHEMA analytics AUTHORIZATION dbo; CREATE TABLE analytics.events (id int, name nvarchar(100)); ``` ### SQLite No schema concept. Each database is a single file with a flat namespace. You can `ATTACH` other database files and query across them using the alias as a prefix: ```sql ATTACH DATABASE 'analytics.db' AS analytics; SELECT * FROM analytics.events; ``` ## Comparison | | PostgreSQL | MySQL | Oracle | MS SQL Server | SQLite | |---|---|---|---|---|---| | Schema = Database? | No | Yes | No | No | N/A (no schemas) | | Schema = User? | No | N/A | Yes | No | N/A | | Default schema | `public` | N/A | user's own | `dbo` | N/A | | Cross-schema queries | Yes | N/A (cross-db) | Yes | Yes | Via `ATTACH` | --- See also: [[Materialized View]], [[ACID]]