Types of Tables in Databases
Database tables are categorized by how they store data and their specific role in your application architecture.
1. Core Persistent Tables
These tables permanently store your business data on disk.
- Standard/Heap Tables: The default type where data is inserted dynamically without a strict physical sorting order.
- Clustered / Index-Organized Tables: Data is physically sorted and stored on disk according to its Primary Key for faster retrieval.
- Partitioned Tables: Large tables split into smaller horizontal pieces based on a specific key (like a date or region) to improve query speed.
- Temporal / History Tables: Automatically track the historical state of data, allowing you to query what a row looked like at a specific point in time.
2. Temporary & Analytical Tables
These are optimized for short-lived data or complex analytics.
- Temporary Tables: Short-lived structures that store intermediate calculation steps and automatically delete themselves when your database session ends.
- Fact Tables: The core of a data warehouse, storing measurable, quantitative metrics (like transaction amounts or click counts).
- Dimension Tables: Accompany fact tables by storing the descriptive context (like product details or customer profiles).
3. Support & System Tables
These keep your database organized, consistent, and running smoothly.
- Lookup / Reference Tables: Store standard, repeating static values (like country codes or order statuses) to enforce data integrity.
- Junction / Bridge Tables: Maps relationships between two other tables to handle many-to-many relationships (like linking a student table to a course table).
- System Tables: Internal tables managed by the database engine to store metadata, user permissions, and schema layouts.
No comments:
Post a Comment