94b421f73a
- Move completed migration docs to docs/archive/ - Move session summaries to docs/archive/sessions/ - Rename API_ASP_DOCUMENTATION.md to docs/API.md - Archive redundant Claude reference files - Update docs/README.md as simplified index - Reduce active docs from 45+ files to 8 essential files Remaining docs: - CLAUDE.md (AI context) - TODO.md (task tracking) - docs/README.md, API.md, QUICK_REFERENCE.md - docs/ASP_DEVELOPMENT_GUIDE.md, STANDARDS.md 🤖 Generated with [Claude Code](https://claude.com/claude-code) Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
358 lines
10 KiB
Markdown
358 lines
10 KiB
Markdown
# Complete Database Refactoring - Executive Summary
|
|
|
|
**Date**: 2025-11-06
|
|
**Status**: DESIGN PHASE - Ready for Review
|
|
**Scope**: MASSIVE - Complete database restructuring
|
|
|
|
---
|
|
|
|
## Overview
|
|
|
|
This document provides a high-level overview of the complete database refactoring project. This is a comprehensive modernization effort that will:
|
|
|
|
1. **Consolidate PCs into Machines** - Eliminate duplicate entity tracking
|
|
2. **Create Generic Communications Infrastructure** - Flexible, extensible communication tracking
|
|
3. **Implement Warranty Management System** - Professional warranty tracking for all assets
|
|
|
|
---
|
|
|
|
## Three Major Components
|
|
|
|
### Component 1: PC-to-Machines Consolidation
|
|
**Document**: `PC_MACHINES_CONSOLIDATION_PLAN.md`
|
|
|
|
**What**: Merge the `pc` table (277 records) into `machines` table (266 records)
|
|
|
|
**Why**:
|
|
- Eliminate duplicate entity tracking
|
|
- Simplify data model
|
|
- Unified asset management
|
|
|
|
**Impact**:
|
|
- 543 total machines after consolidation
|
|
- 10 ASP files need updates (direct queries)
|
|
- 30 ASP files affected (use pc fields/views)
|
|
- 19 database views need updates
|
|
- 6 related tables need updates
|
|
|
|
### Component 2: Communications Infrastructure
|
|
**Document**: `PC_MACHINES_CONSOLIDATION_PLAN.md` (Part 2)
|
|
|
|
**What**: Create generic communications system supporting multiple types
|
|
|
|
**New Tables**:
|
|
- `comstypes` - Communication types (IP, Serial, Network, USB, etc.)
|
|
- `communications` - Universal communication tracking
|
|
|
|
**Features**:
|
|
- Supports IP addresses, serial ports, network interfaces
|
|
- Extensible for future communication types
|
|
- Links to machines via machineid
|
|
- Replaces pc_comm_config and pc_network_interfaces
|
|
|
|
### Component 3: Warranty Management System
|
|
**Document**: `WARRANTY_MANAGEMENT_DESIGN.md`
|
|
|
|
**What**: Professional warranty tracking system for all machines
|
|
|
|
**New Tables**:
|
|
- `warrantytypes` - Types of coverage
|
|
- `warranties` - Individual warranty records (links to existing vendors table)
|
|
- `warrantyhistory` - Audit trail for changes
|
|
|
|
**Features**:
|
|
- Multiple warranties per machine
|
|
- Warranty renewal tracking
|
|
- Automated expiration notifications
|
|
- Cost tracking
|
|
- Historical audit trail
|
|
|
|
---
|
|
|
|
## Database Changes Summary
|
|
|
|
### New Tables (5 total)
|
|
|
|
| Table | Records | Purpose |
|
|
|-------|---------|---------|
|
|
| comstypes | ~7 | Communication type definitions |
|
|
| communications | ~500+ | Machine communication records |
|
|
| warrantytypes | ~8 | Warranty coverage types |
|
|
| warranties | ~277+ | Individual warranty records (uses existing vendors table) |
|
|
| warrantyhistory | ~0 | Warranty change audit trail |
|
|
|
|
### Modified Tables (1)
|
|
|
|
| Table | Changes | New Columns |
|
|
|-------|---------|-------------|
|
|
| machines | Extended for PC data | +9 columns (hostname, serialnumber, osid, pctypeid, etc.) |
|
|
|
|
### Deprecated Tables (To be removed after migration)
|
|
|
|
| Table | Replacement | When to Remove |
|
|
|-------|-------------|----------------|
|
|
| pc | machines | After 30-day testing period |
|
|
| pc_comm_config | communications | After data migration verified |
|
|
| pc_network_interfaces | communications | After data migration verified |
|
|
|
|
### Views to Update (19)
|
|
|
|
All views currently using the `pc` table will be updated to use `machines` with compatibility maintained.
|
|
|
|
---
|
|
|
|
## Migration Execution Order
|
|
|
|
### Phase 1: Infrastructure Setup (Scripts 01-04)
|
|
**Reversible**: Yes
|
|
|
|
1. Script 01: Create communications infrastructure
|
|
2. Script 02: Extend machines table
|
|
3. Script 03: Create PC machine types
|
|
4. Script 04: Create warranty infrastructure
|
|
|
|
**Production Deployment**: Run during maintenance window
|
|
**Estimated Time**: 15 minutes
|
|
**Rollback**: Rollback scripts 01-04 available
|
|
|
|
### Phase 2: Data Migration (Scripts 05-08)
|
|
**Reversible**: Yes (with backups)
|
|
|
|
5. Script 05: Migrate PC data to machines
|
|
6. Script 06: Migrate communication data
|
|
7. Script 07: Migrate warranty data
|
|
8. Script 08: Update relationship tables
|
|
|
|
**Production Deployment**: Run during maintenance window
|
|
**Estimated Time**: 30-45 minutes
|
|
**Rollback**: Restore from backup
|
|
|
|
### Phase 3: Application Updates (Manual)
|
|
**Reversible**: Yes (via version control)
|
|
|
|
9. Update 19 database views
|
|
10. Create compatibility views
|
|
11. Update 10 ASP files (direct queries)
|
|
12. Update 30 ASP files (pc fields/views)
|
|
13. Test all functionality
|
|
|
|
**Production Deployment**: Deploy with application
|
|
**Estimated Time**: 2-3 weeks development + testing
|
|
**Rollback**: Revert code deployment
|
|
|
|
### Phase 4: Cleanup (After 30 days)
|
|
**Reversible**: No - PERMANENT
|
|
|
|
14. Drop pc table
|
|
15. Drop pc_comm_config table
|
|
16. Drop pc_network_interfaces table
|
|
17. Drop compatibility views
|
|
18. Remove deprecated columns
|
|
|
|
**Production Deployment**: Final cleanup
|
|
**Estimated Time**: 15 minutes
|
|
**Rollback**: None (irreversible)
|
|
|
|
---
|
|
|
|
## Complete SQL Script List
|
|
|
|
### Creation Scripts
|
|
1. `sql/01_create_communications_infrastructure.sql`
|
|
2. `sql/02_extend_machines_table.sql`
|
|
3. `sql/03_create_pc_machine_types.sql`
|
|
4. `sql/04_create_warranty_infrastructure.sql`
|
|
|
|
### Migration Scripts (TBD)
|
|
5. `sql/05_migrate_pc_to_machines.sql`
|
|
6. `sql/06_migrate_communications.sql`
|
|
7. `sql/07_migrate_warranties.sql`
|
|
8. `sql/08_update_relationships.sql`
|
|
|
|
### View Scripts (TBD)
|
|
9. `sql/09_update_views.sql`
|
|
10. `sql/10_create_compatibility_views.sql`
|
|
|
|
### Rollback Scripts
|
|
- `sql/ROLLBACK_01_communications_infrastructure.sql`
|
|
- `sql/ROLLBACK_02_machines_table_extensions.sql`
|
|
- `sql/ROLLBACK_03_pc_machine_types.sql` (delete from machinetypes)
|
|
- `sql/ROLLBACK_04_warranty_infrastructure.sql`
|
|
|
|
---
|
|
|
|
## Benefits of This Refactoring
|
|
|
|
### Before Refactoring
|
|
|
|
**Data Model**:
|
|
- Machines and PCs tracked separately
|
|
- Warranty data scattered across PC records
|
|
- Communication data in PC-specific tables
|
|
- Multiple machines types systems (machines.machinetypeid, pc.pctypeid)
|
|
- Rigid communication tracking
|
|
- No warranty history
|
|
- Limited to one warranty per PC
|
|
|
|
**Code**:
|
|
- Duplicate logic for machines vs PCs
|
|
- Complex joins across pc/machines
|
|
- PC-specific ASP files
|
|
- Warranty tracking only for PCs
|
|
|
|
### After Refactoring
|
|
|
|
**Data Model**:
|
|
- Unified machine tracking (all assets)
|
|
- Professional warranty management
|
|
- Generic communication infrastructure
|
|
- Single machine type system
|
|
- Extensible for new communication types
|
|
- Complete warranty audit trail
|
|
- Multiple warranties per machine
|
|
|
|
**Code**:
|
|
- Single codebase for all machines
|
|
- Simpler queries
|
|
- Unified ASP files
|
|
- Warranty tracking for ALL machines
|
|
- Better reporting capabilities
|
|
|
|
---
|
|
|
|
## Risk Assessment
|
|
|
|
| Risk | Severity | Likelihood | Mitigation |
|
|
|------|----------|------------|------------|
|
|
| Data loss during migration | CRITICAL | Low | Full backups, dev testing, rollback scripts |
|
|
| Extended downtime | HIGH | Medium | Parallel testing, staged deployment |
|
|
| View compatibility issues | HIGH | Medium | Compatibility views, thorough testing |
|
|
| Performance degradation | MEDIUM | Low | Indexes optimized, query testing |
|
|
| Application bugs | MEDIUM | Medium | Comprehensive testing, UAT |
|
|
| User confusion | LOW | Medium | Documentation, training |
|
|
|
|
---
|
|
|
|
## Timeline & Effort Estimate
|
|
|
|
| Phase | Duration | Resources |
|
|
|-------|----------|-----------|
|
|
| Design & Review | 1-2 days | COMPLETE |
|
|
| SQL Script Development | 3-5 days | In Progress |
|
|
| Dev Environment Testing | 3-5 days | Pending |
|
|
| ASP File Updates | 5-7 days | Pending |
|
|
| Integration Testing | 3-5 days | Pending |
|
|
| User Acceptance Testing | 2-3 days | Pending |
|
|
| Production Deployment | 1 day | Pending |
|
|
| Monitoring Period | 30 days | Pending |
|
|
| Final Cleanup | 1 day | Pending |
|
|
| **TOTAL** | **20-35 days** | |
|
|
|
|
---
|
|
|
|
## Key Success Metrics
|
|
|
|
### Data Integrity
|
|
- [ ] All 277 PCs migrated to machines
|
|
- [ ] All communication records migrated
|
|
- [ ] All warranty records migrated
|
|
- [ ] All FK relationships intact
|
|
- [ ] Zero data loss
|
|
|
|
### Functionality
|
|
- [ ] All 19 views return correct data
|
|
- [ ] All ASP files working correctly
|
|
- [ ] Warranty tracking functional
|
|
- [ ] Communication tracking functional
|
|
- [ ] Reports generating correctly
|
|
|
|
### Performance
|
|
- [ ] Query performance same or better
|
|
- [ ] Page load times acceptable
|
|
- [ ] Database size reduced (from consolidation)
|
|
|
|
---
|
|
|
|
## Production Deployment Checklist
|
|
|
|
### Pre-Deployment
|
|
- [ ] All SQL scripts tested on dev
|
|
- [ ] All ASP files tested
|
|
- [ ] All views validated
|
|
- [ ] Performance testing complete
|
|
- [ ] UAT sign-off received
|
|
- [ ] Rollback plan documented
|
|
- [ ] Full database backup created
|
|
- [ ] Maintenance window scheduled
|
|
- [ ] Users notified
|
|
|
|
### Deployment Day
|
|
- [ ] Verify backup completed
|
|
- [ ] Run scripts 01-04 (infrastructure)
|
|
- [ ] Verify tables created
|
|
- [ ] Run scripts 05-08 (data migration)
|
|
- [ ] Verify data migrated
|
|
- [ ] Update views
|
|
- [ ] Deploy ASP files
|
|
- [ ] Smoke test functionality
|
|
- [ ] Monitor for errors
|
|
|
|
### Post-Deployment
|
|
- [ ] Verify all functionality
|
|
- [ ] Monitor performance
|
|
- [ ] Check error logs
|
|
- [ ] User feedback collection
|
|
- [ ] Document issues
|
|
- [ ] Plan fixes if needed
|
|
|
|
### 30-Day Cleanup
|
|
- [ ] Verify stability
|
|
- [ ] Run cleanup scripts
|
|
- [ ] Remove old tables
|
|
- [ ] Update documentation
|
|
- [ ] Close project
|
|
|
|
---
|
|
|
|
## Documentation Index
|
|
|
|
1. **PC_MACHINES_CONSOLIDATION_PLAN.md** - Complete PC consolidation design
|
|
2. **WARRANTY_MANAGEMENT_DESIGN.md** - Warranty system design
|
|
3. **COMPLETE_REFACTORING_SUMMARY.md** - This document (overview)
|
|
|
|
---
|
|
|
|
## Questions & Approvals
|
|
|
|
### Design Decisions Needed
|
|
- [ ] Approve warranty system design
|
|
- [ ] Approve communications infrastructure
|
|
- [ ] Approve PC machine type mapping
|
|
- [ ] Set maintenance window date
|
|
|
|
### Approvals Required
|
|
- [ ] Database Administrator
|
|
- [ ] Lead Developer
|
|
- [ ] System Owner
|
|
- [ ] Business Stakeholder
|
|
|
|
---
|
|
|
|
## Support & Contact
|
|
|
|
**For questions about this refactoring:**
|
|
- Review design documents first
|
|
- Check rollback procedures
|
|
- Test on dev environment
|
|
- Contact system administrator
|
|
|
|
---
|
|
|
|
**Status**: DESIGN PHASE COMPLETE
|
|
**Next Step**: Create migration scripts (scripts 05-08)
|
|
**Target Deployment**: TBD
|
|
|
|
---
|
|
|
|
*This is a living document and will be updated as the project progresses.*
|