File & Database Design
Database design is the process of defining the structure, storage, and relationships of data in a system. A well-designed database ensures data integrity, minimizes redundancy, and supports efficient retrieval and updates.
File-Based vs Database Systems
| Aspect | File-Based | Database |
|---|---|---|
| Redundancy | High (data duplicated across files) | Minimal (normalized) |
| Data Integrity | Difficult to enforce | Enforced via constraints |
| Concurrent Access | Limited | Full support with locking |
| Security | File-level only | Granular access control |
Normalization
Normalization is the process of organizing data to reduce redundancy and improve data integrity. It involves dividing large tables into smaller, related tables and defining relationships between them.
Normal Forms:
1NF: Eliminate repeating groups
Each cell contains a single value
Each record is unique
2NF: Meet 1NF + eliminate partial dependencies
Every non-key attribute depends on the entire primary key
3NF: Meet 2NF + eliminate transitive dependencies
Non-key attributes depend only on the primary key
Example of normalization:
UNNORMALIZED: Order(OrderID, CustomerName, Product1, Product2, ...)
1NF: Order(OrderID, CustomerName, Product, Quantity)
2NF: Order(OrderID, Product) + Customer(CustomerID, Name)
3NF: Order(OrderID, CustomerID, Product) + Customer(...) + Product(...)Database Design Steps
- Create the ERD from requirements
- Map entities to tables
- Define primary keys and foreign keys
- Apply normalization rules
- Define constraints (NOT NULL, UNIQUE, CHECK, FK)
- Create indexes for frequently queried columns
- Design views for specific user needs
- Test with sample data
Physical Design Considerations
- Storage allocation: How tables and indexes are stored on disk
- Indexing strategy: Speeding up queries without slowing writes
- Partitioning: Splitting large tables for performance
- Backup and recovery: Protecting data against loss
- Performance tuning: Optimizing queries and configurations
Summary
Proper file and database design ensures that data is stored efficiently, remains consistent, and can be retrieved quickly. Normalization and careful physical design are essential for building reliable data storage foundations.