Database Design

Hey students! 👋 Welcome to one of the most important topics in computer science - database design! In this lesson, you'll learn how to create efficient, well-structured databases that can handle real-world data effectively. We'll explore Entity-Relationship (ER) modeling, schema design, and how to map conceptual designs to actual database tables. By the end of this lesson, you'll understand the fundamental principles that power everything from your school's student information system to massive platforms like Instagram and Netflix. Let's dive into the fascinating world of database architecture! 🚀

Understanding Entity-Relationship (ER) Modeling

Entity-Relationship modeling is like creating a blueprint for your database before you actually build it. Think of it as designing the floor plan of a house before construction begins - you need to know where everything goes and how different rooms connect to each other.

An entity represents a real-world object or concept that you want to store information about. For example, in a school database, entities might include Students, Teachers, Courses, and Classrooms. Each entity has attributes - these are the specific pieces of information you want to store. A Student entity might have attributes like StudentID, Name, Email, DateOfBirth, and GPA.

Relationships show how entities connect to each other. In our school example, Students enroll in Courses, Teachers teach Courses, and Courses are held in Classrooms. These relationships can be one-to-one (1:1), one-to-many (1:M), or many-to-many (M:N). For instance, one Teacher can teach many Courses (1:M), but one Course can also have many Students enrolled (M:N).

Real-world example: Netflix uses ER modeling extensively! They have entities like Users, Movies, Genres, and Actors. The relationships include Users watch Movies, Movies belong to Genres, and Actors appear in Movies. This structure allows Netflix to recommend content based on your viewing history and preferences.

When creating ER diagrams, we use specific symbols: rectangles for entities, ovals for attributes, diamonds for relationships, and lines to connect them. Primary keys (unique identifiers) are underlined, helping us distinguish each record uniquely.

Schema Design and Normalization

Once you've created your ER model, the next step is designing the database schema - the actual structure of your database tables. This process involves normalization, which is like organizing your closet to eliminate clutter and make everything easy to find.

Normalization follows specific rules called normal forms. The most important ones for AS-level are:

First Normal Form (1NF) eliminates repeating groups. Instead of having a single row with multiple phone numbers like "John Smith, 555-1234, 555-5678, 555-9999", you create separate rows for each phone number.

Second Normal Form (2NF) eliminates partial dependencies. This means every non-key attribute must depend on the entire primary key, not just part of it. For example, if you have a table with StudentID and CourseID as a composite primary key, the student's name should only depend on StudentID, not the combination.

Third Normal Form (3NF) eliminates transitive dependencies. If StudentID determines DepartmentID, and DepartmentID determines DepartmentName, then DepartmentName shouldn't be stored in the Student table - it belongs in a separate Department table.

Consider Amazon's product database: instead of storing all product information in one massive table, they normalize it. Products have basic information (name, price), Categories have their own table, and Reviews are stored separately. This prevents data redundancy - if a category name changes, it only needs updating in one place, not thousands of product records.

Mapping ER Models to Relational Tables

Transforming your ER model into actual database tables follows systematic rules. Strong entities become tables directly - each entity becomes a table, and each attribute becomes a column. The entity's primary key becomes the table's primary key.

Weak entities (entities that depend on another entity for their existence) require special handling. For example, OrderItems are weak entities that depend on Orders. The OrderItem table includes both its own attributes and the primary key from the Order table as a foreign key.

Relationships are mapped differently based on their cardinality:

• 1:1 relationships: Add the primary key of one table as a foreign key in the other
• 1:M relationships: Add the primary key of the "one" side as a foreign key in the "many" side
• M:N relationships: Create a new junction table containing the primary keys of both entities

Let's look at Instagram's approach: Users can follow many other Users (M:N relationship). Instagram creates a "Follows" table with UserID and FollowedUserID columns. This allows them to efficiently track who follows whom without duplicating user information.

Composite attributes (attributes with multiple parts, like Address containing Street, City, State) can be handled by either creating separate columns for each part or storing the composite value as a single field, depending on how you'll use the data.

Performance and Scalability Considerations

Database performance isn't just about making queries run fast - it's about ensuring your database can handle growth and maintain speed as data volume increases. Think of it like designing a highway system that works for both current traffic and future expansion.

Indexing is your primary performance tool. An index is like a book's table of contents - it helps the database find information quickly without scanning every row. However, indexes consume storage space and slow down insert/update operations, so you need to balance query speed with modification performance. Primary keys are automatically indexed, but you should also index frequently searched columns like email addresses or product names.

Query optimization involves writing efficient SQL statements. Using specific column names instead of SELECT *, adding appropriate WHERE clauses, and avoiding unnecessary JOINs all improve performance. For example, instead of retrieving all student records and filtering in your application, use WHERE clauses to let the database do the filtering.

Scalability planning means designing for growth. Vertical scaling (upgrading hardware) has limits, so modern systems use horizontal scaling (distributing data across multiple servers). Techniques include:

• Partitioning: Splitting large tables across multiple storage locations
• Replication: Maintaining copies of data for backup and load distribution
• Sharding: Distributing data based on specific criteria (like geographic location)

Facebook handles billions of users by partitioning their data geographically and by user activity patterns. They also use read replicas to handle the massive number of timeline queries while keeping the main database available for updates.

Denormalization sometimes improves performance by strategically violating normalization rules. For frequently accessed data that rarely changes, storing redundant information can eliminate expensive JOIN operations. However, this requires careful consideration of the trade-offs between query speed and data consistency.

Conclusion

Database design is the foundation of effective data management, combining conceptual modeling with practical implementation considerations. You've learned how ER modeling helps visualize data relationships, how normalization ensures data integrity and eliminates redundancy, and how to transform conceptual designs into efficient relational tables. Understanding performance and scalability considerations prepares you for real-world database challenges where systems must handle growing data volumes and user loads. These skills form the backbone of modern information systems, from small business applications to global platforms serving millions of users.

Study Notes

• Entity: Real-world object or concept stored in database (Students, Products, Orders)

• Attribute: Specific data about an entity (Name, Price, Date)

• Relationship: Connection between entities with cardinality (1:1, 1:M, M:N)

• Primary Key: Unique identifier for each record in a table

• Foreign Key: Primary key from another table used to establish relationships

• First Normal Form (1NF): Eliminate repeating groups and ensure atomic values

• Second Normal Form (2NF): Eliminate partial dependencies on composite keys

• Third Normal Form (3NF): Eliminate transitive dependencies

• Strong Entity: Independent entity that becomes its own table

• Weak Entity: Dependent entity requiring foreign key from strong entity

• Junction Table: Handles M:N relationships by storing primary keys from both entities

• Index: Database structure that speeds up data retrieval but slows modifications

• Vertical Scaling: Upgrading hardware capacity (CPU, RAM, storage)

• Horizontal Scaling: Distributing data across multiple servers

• Partitioning: Splitting large tables across multiple storage locations

• Denormalization: Strategic violation of normalization rules to improve query performance

• Composite Attribute: Multi-part attribute like Address (Street, City, State, ZIP)