Planning a Database System | Requirements Analysis & Strategies

# CHAPTER 4

Planning a Database System #

1. Introduction #

The most catastrophic mistakes in database design do not happen when typing SQL code; they happen during the planning phase. If you design a database for a hospital, and you assume a Patient can only ever have one Doctor, but the real-world hospital allows a Patient to have multiple Doctors, your entire database structure is invalid. You will have to delete the tables, rewrite the backend code, and lose weeks of work. In this chapter, we will learn how Database Architects perform Requirement Analysis and translate human business processes into a logical database plan.

2. Learning Objectives #

By the end of this chapter, you will be able to:

• Understand the Requirement Analysis phase.

• Gather information from stakeholders.

• Define strict Business Rules.

• Translate Business Rules into logical Entities.

• Avoid the "Assumption Trap" in database design.

3. Step 1: Requirement Analysis #

Before drawing diagrams or writing SQL, the architect must become an expert in the business domain they are modeling. If you are building software for a Library, you must interview the librarians.

• *What data do you need to store about a book?* (Title, Author, ISBN, Publish Year).

• *What data do you need for a member?* (Name, Address, Phone, Membership Date).

• *How long can a member check out a book?* (14 days).

You collect these answers in a Requirements Document.

4. Step 2: Defining Business Rules #

A Database is simply a mathematical reflection of physical business operations. Business Rules are brief, precise statements that define or constrain those operations. They are the most critical part of database design because they dictate exactly how your tables must relate to each other.

Examples of Business Rules:

1. 1. A Customer may place many Orders.

1. 2. An Order must belong to exactly one Customer.

1. 3. An Order may contain many Products.

1. 4. A Product may belong to many Orders.

1. 5. An Employee must belong to one Department.

5. Step 3: Extracting Entities and Attributes #

Once you have your written Business Rules, you perform a linguistic analysis to identify your database structures.

• Nouns become Entities (Tables).

• Adjectives/Characteristics become Attributes (Columns).

• Verbs become Relationships (Foreign Keys).

*Example Sentence:* "A Customer (Noun/Table) with an Email Address (Attribute/Column) places (Verb/Relationship) an Order (Noun/Table)."

6. The "Assumption Trap" #

The greatest danger in database design is the architect making assumptions without asking the business owners.

*Scenario:* You are designing an HR database. You create an employees table and you add a desknumber column. You *assume* one employee has one desk. *The Reality:* The company uses "hot-desking" where employees rotate desks daily. Your database architecture is now physically incapable of tracking the company's daily reality. Rule: Never assume a 1-to-1 or 1-to-Many relationship without explicitly verifying the Business Rules with the stakeholders.

7. Mini Project: Plan an Ecommerce Database #

Let's analyze the requirements for a simple online store.

1. Requirement Gathering (Interviewing the CEO): "We sell electronics. Users register on the site. A user can add items to a cart and checkout. We need to track the date they bought it, and how much they paid."

2. Extracting Business Rules:

• A User can place multiple Orders.

• An Order is placed by exactly one User.

• An Order can contain multiple Products.

• A Product can be in multiple Orders.

3. Defining the Entities (Tables):

• Table: Users (Attributes: id, name, email)

• Table: Products (Attributes: id, name, currentprice)

• Table: Orders (Attributes: id, userid, orderdate)

• Table: OrderItems (Attributes: orderid, product_id, quantity) *(Note: We need this pivot table because of the Many-to-Many rule!)*

8. Data Flow Planning #

Beyond just storing data, an architect must think about how the data flows.

• *Write-Heavy vs Read-Heavy:* Will this database receive 10,000 new records a second (like IoT sensors)? Or will it receive 10 writes a day, but be read 10,000 times a second (like a blog)? This dictates your indexing strategy (Chapter 14).

• *Data Retention:* Does this data need to be kept forever for legal reasons, or can we delete logs after 30 days?

9. Common Mistakes #

• Designing the Database based on the UI: A junior developer might look at a complex website dashboard and try to design a single database table that exactly matches the UI layout. UI layouts change every month. A database schema should model the core *Business Logic*, not the User Interface.

10. Best Practices #

• Iterative Planning: Do not attempt to plan a 500-table enterprise architecture in one sitting. Break the system down into modules (e.g., The Authentication Module, The Billing Module, The Inventory Module) and plan them iteratively.

11. Exercises #

1. 1. In linguistic requirement analysis, what database structural element do "Nouns" typically translate into?

1. 2. What is a "Business Rule" and why is it critical to database design?

12. Database Design Challenges #

You are interviewing the owner of a Veterinary Clinic. They tell you: *"Our Clinic has many Vets. A Vet treats many Pets. A Pet is owned by an Owner. An Owner can have multiple Pets."* Write out the 3 primary Business Rules defining the relationships between Vets, Pets, and Owners based on this statement. *(Answer: 1. A Vet treats many Pets, and a Pet can be treated by many Vets. 2. An Owner has many Pets. 3. A Pet belongs to exactly one Owner).*

13. MCQ Quiz with Answers #

14. Interview Questions #

• Q: Describe the "Assumption Trap" in database design. Provide a real-world scenario where an architect making an assumption about a Business Rule leads to a catastrophic schema failure.

• Q: Explain the difference between designing a database to model "Business Logic" versus designing it to model the "User Interface." Why is the latter an anti-pattern?

15. FAQs #

Q: What if the Business Rules change after the database is built? A: This happens all the time! (e.g., A company used to only allow 1 email per user, now they want to allow multiple). You execute a "Database Migration" using SQL ALTER TABLE commands to restructure the physical tables and migrate the old data to fit the new rules.

16. Summary #

You are now an analyst. By interviewing stakeholders, extracting precise Business Rules, translating nouns into Entities, and avoiding dangerous architectural assumptions, you can generate a flawless logical plan that perfectly mirrors the real-world business.

17. Next Chapter Recommendation #

We have our logical plan written down in English. Now, we must translate it into database theory. In Chapter 5: Entities, Attributes, and Relationships, we will formalize these concepts and understand the critical mechanics of Entity Cardinality.