Database schema design significantly impacts application maintainability, query performance, and security posture. A poorly designed schema creates compounding problems over time.
This document examines a real-world case: a BPO operational database that grew organically to 66 columns in a single table, and the redesign process that produced a normalized, secure alternative.
The Original Problem
The staff_onboarding table contained every piece of employee information:
`sql
-- Partial list of the 66 columns
staff_onboarding (
id UUID,
first_name TEXT,
last_name TEXT,
email TEXT,
phone TEXT,
sss_number TEXT,
philhealth_number TEXT,
pagibig_number TEXT,
tin_number TEXT,
bank_name TEXT,
bank_account_number TEXT,
bank_account_type TEXT,
emergency_contact_name TEXT,
emergency_contact_phone TEXT,
emergency_contact_relationship TEXT,
current_salary DECIMAL,
previous_salary DECIMAL,
last_salary_increase_date DATE,
salary_increase_amount DECIMAL,
probationary_end_date DATE,
regularization_date DATE,
-- ... 44 more columns
)
`
Problems with This Design
- 1.No separation of concerns — Sensitive data (SSS numbers) mixed with operational data (assignment status)
- 1.Audit limitations — Salary changes overwrote previous values; no history preserved
- 1.Access control complexity — Row-level security couldn't distinguish between someone who needs emergency contacts versus someone who needs government IDs
- 1.Query inefficiency — Every query returned all 66 columns even when only a few were needed
- 1.Maintenance burden — Adding new attributes meant altering a critical table
Design Principles
Separate by Access Pattern
Different users need different data: - HR needs emergency contacts and personal documents - Finance needs bank accounts and salary information - Operations needs assignment status and availability
Structuring tables around access patterns simplifies security policies.
Append, Don't Update
For auditable data (salaries, employment status), insert new records rather than updating existing ones. This preserves complete history.
Minimize Column Count
Tables should have a clear, focused purpose. If a table serves multiple purposes, it should probably be multiple tables.
The Normalized Schema
Core Identity Table
`sql
CREATE TABLE staff (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
phone TEXT,
status TEXT DEFAULT 'active',
hired_at DATE,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_staff_email ON staff(email);
CREATE INDEX idx_staff_status ON staff(status);
`
This table contains only identity information—the minimum needed to identify a person.
Government Identification
`sql
CREATE TABLE staff_government_ids (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
staff_id UUID UNIQUE REFERENCES staff(id) ON DELETE CASCADE,
sss_number TEXT,
philhealth_number TEXT,
pagibig_number TEXT,
tin_number TEXT,
verified_at TIMESTAMPTZ,
verified_by UUID REFERENCES staff(id),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
`
One-to-one relationship with staff. Separate table enables: - Different RLS policies (only HR and the employee can see) - Audit of verification status - Clear ownership of sensitive data
Bank Accounts
`sql
CREATE TABLE staff_bank_accounts (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
staff_id UUID REFERENCES staff(id) ON DELETE CASCADE,
bank_name TEXT NOT NULL,
account_number TEXT NOT NULL,
account_type TEXT,
is_primary BOOLEAN DEFAULT false,
verified_at TIMESTAMPTZ,
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_staff_bank_accounts_staff ON staff_bank_accounts(staff_id);
`
One-to-many relationship—staff can have multiple bank accounts with one marked as primary.
Emergency Contacts
`sql
CREATE TABLE staff_emergency_contacts (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
staff_id UUID REFERENCES staff(id) ON DELETE CASCADE,
name TEXT NOT NULL,
phone TEXT NOT NULL,
relationship TEXT,
priority INTEGER DEFAULT 1,
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_staff_emergency_contacts_staff ON staff_emergency_contacts(staff_id);
`
Multiple contacts with priority ordering.
Salary History
`sql
CREATE TABLE staff_salary_history (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
staff_id UUID REFERENCES staff(id) ON DELETE CASCADE,
amount DECIMAL(12,2) NOT NULL,
currency TEXT DEFAULT 'PHP',
effective_date DATE NOT NULL,
reason TEXT,
approved_by UUID REFERENCES staff(id),
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_staff_salary_history_staff ON staff_salary_history(staff_id);
CREATE INDEX idx_staff_salary_history_date ON staff_salary_history(effective_date DESC);
`
Append-only table. Current salary is the most recent record by effective_date. Complete history preserved.
Employment Status History
`sql
CREATE TABLE staff_status_history (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
staff_id UUID REFERENCES staff(id) ON DELETE CASCADE,
status TEXT NOT NULL,
effective_date DATE NOT NULL,
notes TEXT,
recorded_by UUID REFERENCES staff(id),
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Status values: probationary, regular, on_leave, resigned, terminated
`
Tracks all status transitions with dates and notes.
Client Assignments
`sql
CREATE TABLE assignments (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
staff_id UUID REFERENCES staff(id) ON DELETE CASCADE,
client_id UUID REFERENCES clients(id) ON DELETE CASCADE,
role TEXT NOT NULL,
start_date DATE NOT NULL,
end_date DATE,
status TEXT DEFAULT 'active',
hourly_rate DECIMAL(10,2),
monthly_rate DECIMAL(10,2),
created_at TIMESTAMPTZ DEFAULT NOW(),
UNIQUE(staff_id, client_id, start_date)
);
CREATE INDEX idx_assignments_staff ON assignments(staff_id);
CREATE INDEX idx_assignments_client ON assignments(client_id);
CREATE INDEX idx_assignments_status ON assignments(status);
`
Links staff to clients. The unique constraint prevents duplicate assignments starting on the same date.
Row Level Security
With tables separated by access pattern, RLS policies are straightforward:
Staff Can See Their Own Data
`sql
-- Government IDs
ALTER TABLE staff_government_ids ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Staff can view own government IDs" ON staff_government_ids FOR SELECT USING (staff_id = auth.uid());
-- Salary history ALTER TABLE staff_salary_history ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Staff can view own salary history"
ON staff_salary_history
FOR SELECT
USING (staff_id = auth.uid());
`
HR Can See All Staff Data
`sql
CREATE POLICY "HR can view all government IDs"
ON staff_government_ids
FOR ALL
USING (
EXISTS (
SELECT 1 FROM staff_roles
WHERE user_id = auth.uid()
AND role = 'hr'
)
);
`
Managers Can See Their Team's Assignments
`sql
CREATE POLICY "Managers can view team assignments"
ON assignments
FOR SELECT
USING (
EXISTS (
SELECT 1 FROM team_memberships
WHERE manager_id = auth.uid()
AND staff_id = assignments.staff_id
)
);
`
Querying Patterns
Get Current Salary
`sql
SELECT amount, effective_date
FROM staff_salary_history
WHERE staff_id = $1
ORDER BY effective_date DESC
LIMIT 1;
`
Get Full Staff Profile (for authorized users)
`sql
SELECT
s.*,
g.sss_number,
g.philhealth_number,
(
SELECT json_agg(json_build_object(
'name', ec.name,
'phone', ec.phone,
'relationship', ec.relationship
) ORDER BY ec.priority)
FROM staff_emergency_contacts ec
WHERE ec.staff_id = s.id
) AS emergency_contacts,
(
SELECT amount
FROM staff_salary_history sh
WHERE sh.staff_id = s.id
ORDER BY effective_date DESC
LIMIT 1
) AS current_salary
FROM staff s
LEFT JOIN staff_government_ids g ON g.staff_id = s.id
WHERE s.id = $1;
`
View for Common Operations
`sql
CREATE VIEW staff_current_status AS
SELECT
s.id,
s.first_name,
s.last_name,
s.email,
s.status,
(
SELECT amount FROM staff_salary_history
WHERE staff_id = s.id
ORDER BY effective_date DESC LIMIT 1
) AS current_salary,
(
SELECT json_agg(json_build_object(
'client_id', a.client_id,
'role', a.role,
'monthly_rate', a.monthly_rate
))
FROM assignments a
WHERE a.staff_id = s.id AND a.status = 'active'
) AS active_assignments
FROM staff s;
`
Migration Strategy
Converting from 66 columns to normalized tables requires careful migration:
Phase 1: Create New Tables
Create all target tables without modifying the original.
Phase 2: Migrate Data
`sql
-- Migrate government IDs
INSERT INTO staff_government_ids (staff_id, sss_number, philhealth_number, pagibig_number, tin_number)
SELECT id, sss_number, philhealth_number, pagibig_number, tin_number
FROM staff_onboarding
WHERE sss_number IS NOT NULL
OR philhealth_number IS NOT NULL
OR pagibig_number IS NOT NULL
OR tin_number IS NOT NULL;
-- Migrate bank accounts INSERT INTO staff_bank_accounts (staff_id, bank_name, account_number, account_type, is_primary) SELECT id, bank_name, bank_account_number, bank_account_type, true FROM staff_onboarding WHERE bank_name IS NOT NULL;
-- Migrate current salary as initial history entry
INSERT INTO staff_salary_history (staff_id, amount, effective_date, reason)
SELECT id, current_salary, hired_at, 'Initial salary - migrated from legacy system'
FROM staff_onboarding
WHERE current_salary IS NOT NULL;
`
Phase 3: Verification
`sql
-- Verify row counts match
SELECT
(SELECT COUNT(*) FROM staff_onboarding WHERE sss_number IS NOT NULL) AS old_with_sss,
(SELECT COUNT(*) FROM staff_government_ids WHERE sss_number IS NOT NULL) AS new_with_sss;
`
Phase 4: Update Application Code
Modify queries to use new tables. Deploy with feature flag if possible.
Phase 5: Deprecate Original Table
`sql
ALTER TABLE staff_onboarding RENAME TO staff_onboarding_deprecated;
`
Retain for 90 days, then archive or delete.
Performance Comparison
| Operation | 66-Column Table | Normalized | |-----------|-----------------|------------| | Select staff list | 4.2 KB/row | 0.3 KB/row | | Get salary only | Full row scan | Direct index lookup | | Update salary | Row lock | Append (no lock) | | Add new attribute | ALTER TABLE | New table row |
The reduction in data transfer per query significantly improves application responsiveness.
FAQ
Why not just add columns to the staff table as needed?
Each column added increases row size for all queries. At 66 columns, every SELECT returns substantial unnecessary data. Normalized tables return only what's requested.
Doesn't normalization increase query complexity?
Yes, queries require joins. However, views encapsulate common access patterns, and the performance benefits of smaller row sizes typically outweigh join costs.
How do you handle backward compatibility during migration?
Create views that mimic the old structure for applications that can't be immediately updated:
`sql
CREATE VIEW staff_onboarding_compat AS
SELECT
s.*,
g.sss_number,
g.philhealth_number,
b.bank_name,
b.account_number AS bank_account_number
FROM staff s
LEFT JOIN staff_government_ids g ON g.staff_id = s.id
LEFT JOIN staff_bank_accounts b ON b.staff_id = s.id AND b.is_primary = true;
`
What about the original table's data volume?
The 66-column table with 800 rows consumed approximately 3.4 MB. The normalized structure with the same data consumes approximately 400 KB across all tables—an 88% reduction.
Schema design decisions compound over time. Investing in proper structure early prevents exponentially increasing maintenance costs later.