6. Schemes and Namespaces

Schemas and Namespaces in Databases

Schemas and namespaces help organize database objects, avoid naming conflicts, and improve security.

---

1. What is a Schema?

A schema is a logical container that groups database objects like tables, views, indexes, stored procedures, and functions. It helps organize and manage data within a database.

Example: Schema in PostgreSQL

CREATE SCHEMA sales;
CREATE TABLE sales.orders (
    order_id SERIAL PRIMARY KEY,
    customer_name TEXT,
    total_amount DECIMAL
);

Here, the sales schema contains the orders table.

---

2. What is a Namespace?

A namespace is a broader term referring to how database objects are uniquely identified within a system. Schemas act as namespaces in relational databases.

For example, in PostgreSQL and SQL Server, you can have multiple schemas, each acting as a separate namespace:

sales.orders  -- Table in 'sales' schema
hr.employees  -- Table in 'hr' schema
public.users  -- Default schema

---

3. Benefits of Schemas and Namespaces

✅ Better Organization – Separates data logically (e.g., sales, hr).
✅ Avoids Naming Conflicts – Two tables with the same name can exist in different schemas (sales.orders vs. hr.orders).
✅ Access Control & Security – Different permissions for different schemas.
✅ Easier Management – Can back up, restore, or migrate specific schemas.

---

4. Schema vs. Database

Feature	Schema	Database
Scope	Part of a database	Standalone system
Contains	Tables, views, functions	Multiple schemas, users, connections
Isolation	Objects within a database	Separate storage, users, permissions
Usage	Organizing related objects	Managing multiple projects/environments

---

5. Schema Management Commands

Create a Schema

CREATE SCHEMA hr;

Move a Table to a Schema

ALTER TABLE employees SET SCHEMA hr;

Grant Access to a Schema

GRANT USAGE ON SCHEMA hr TO analyst;

Schemas provide logical separation in a database, while namespaces ensure unique identification of objects, preventing conflicts.