Database Design Fundamentals for Web Applications
Learn the core principles of relational database design that keep applications reliable and performant.
Database design is one of the most consequential decisions in building a web application. A well-designed schema makes the application fast, reliable, and easy to extend. A poorly designed schema leads to data inconsistencies, slow queries, and painful migrations.
Normalization is the process of organizing data to reduce redundancy. In a normalized database, each piece of information is stored in exactly one place. For example, in the YR NOVATECH platform, an intern's domain (like 'Full Stack Development') is stored in a 'domains' table, and the internship record references it via a foreign key. If the domain name changes, we update it in one place and all internship records automatically reflect the change.
Primary keys uniquely identify each row. We use UUIDs (Universally Unique Identifiers) rather than auto-incrementing integers. UUIDs can be generated anywhere without coordination, which is essential for distributed systems and prevents enumeration attacks (where someone guesses IDs like /users/1, /users/2, etc.).
Foreign keys enforce relationships between tables. When an internship record references a student_id, the foreign key constraint ensures that student actually exists in the profiles table. Without foreign keys, you risk 'orphaned' records — an internship pointing to a user who was deleted. These inconsistencies are extremely difficult to fix after the fact.
Indexes dramatically improve query performance. Without an index, the database must scan every row to find matching records (a 'full table scan'). With a proper index, it can jump directly to the relevant rows. However, indexes have a cost — they slow down INSERT and UPDATE operations and consume storage. The key is indexing columns that are frequently used in WHERE clauses, JOIN conditions, and ORDER BY clauses.
Row Level Security (RLS) is a PostgreSQL feature we use extensively at YR NOVATECH. RLS policies define rules about which rows each user can read, insert, update, or delete. For example, an intern can only see their own internship record, while an admin can see all records. These policies are enforced at the database level, which means even if there's a bug in the application code, users still can't access data they shouldn't see.