Database Design and Good Practices

Lesson 8 of 8 · Database Fundamentals · Beginner

A database that works well today can become unreliable, hard to maintain, or difficult to query if it was not designed thoughtfully. This final lesson covers the principles of good database design and the habits that keep a database trustworthy over time.

Start with Purpose

Before creating any tables, define what the database is for. What questions does it need to answer? Who will use it? What data will be entered, and by whom? A clear purpose prevents scope creep and guides every design decision that follows.

Identify Entities and Define Tables

List the real-world things your database needs to track — programs, participants, locations, registrations. Each distinct entity becomes a table. Avoid creating tables for things that are simply attributes of another entity.

Choose Data Types and Constraints Carefully

For each column, choose the most appropriate data type. Use date columns for dates, integer columns for counts, and text columns for names and descriptions. Apply NOT NULL constraints to fields that must always have a value. Apply UNIQUE constraints where duplicate values would be an error.

Select Primary Keys

Every table needs a primary key. Prefer surrogate keys (auto-generated integers) for stability. Avoid using sensitive personal information as a primary key.

Define Relationships

Identify which tables are related and add foreign keys to enforce those relationships. Define whether referential integrity should prevent deletion of parent records that have children, or whether deletions should cascade.

Normalization at a Beginner Level

Normalization means organizing data to reduce repetition and improve consistency. At a beginner level, the key principle is: store each fact in exactly one place. If the same information appears in multiple rows, it probably belongs in its own table.

Avoid storing repeated groups of columns — for example, phone1, phone2, phone3. Instead, create a separate phone_numbers table with a foreign key back to the main record.

Consistent Naming

Use clear, consistent names for tables and columns. Common conventions include lowercase with underscores (program_name), singular table names (program not programs), and descriptive column names that make the data type obvious (start_date not date1). Pick a convention and apply it consistently.

Documentation and Data Dictionaries

Document what each table and column contains, what values are valid, and what the data represents. A data dictionary is a structured record of this information. Without documentation, future users — including yourself — will not know what the data means.

Backups and Restoration Testing

Back up your database regularly. Test that backups can actually be restored — a backup you have never tested may not work when you need it. Store backups in a separate location from the live database.

Access Control and Privacy

Grant users only the permissions they need — read access for those who only query, write access only for those who must enter or update data. Do not store sensitive personal information unless it is necessary for the database's purpose. Apply appropriate access controls to sensitive columns.

Beginner Design Checklist

• ☐ Purpose is defined and documented
• ☐ One table per entity type
• ☐ Correct data types for every column
• ☐ NOT NULL constraints on required fields
• ☐ Primary key on every table
• ☐ Foreign keys defined for all relationships
• ☐ No repeated groups of columns
• ☐ Consistent naming convention applied
• ☐ Data dictionary created
• ☐ Backup process in place and tested
• ☐ Access permissions reviewed
• ☐ Sensitive data minimized and protected

Key Takeaways

• Define the database purpose before creating any tables.
• One table per entity; store each fact in exactly one place.
• Choose correct data types and apply appropriate constraints.
• Document tables, columns, and valid values in a data dictionary.
• Back up regularly, test restoration, and apply least-privilege access control.