Web Builder

Database Design

Project: School Management Website
✓ Document Approved
This document has been approved and is currently locked.
Document Title
Status
Document Content
# DATABASE DESIGN DOCUMENT

**Project Name:** School Management Website
**Document Version:** 1.1.0
**Stage:** Database Design
**Author:** Software Project Architect
**Status:** Under Review

---

## 1. Overview & Objectives

The purpose of this document is to define the database structure required for the School Management Website based on the approved Project Understanding, Project Proposal and Logical Roadmap.

The database design should support the confirmed student management requirements while keeping technical decisions that have not yet been finalized clearly identified as recommendations or open decisions.

This document does not introduce new business requirements that were not confirmed in the previous project stages.

---

# 2. Confirmed Data Requirements

The following student information was explicitly confirmed in the project requirements:

* Student Name
* Student Email
* Student Phone
* Student Course
* Student Admission Date

The system must support:

* Adding student records
* Viewing student records
* Editing student records
* Deleting student records
* Searching student records
* Filtering student records

### Important

The exact database implementation of search, filtering and deletion has not yet been finalized.

Therefore, specific search fields, filter fields and deletion strategy remain pending decisions.

---

# 3. Proposed Database Structure

## 3.1 Student Data

The project requires persistent storage for student records.

### Baseline Table

**Table Name:** `students`

The following fields correspond directly to confirmed student data requirements:

| Field          | Proposed Data Type | Requirement Status | Notes                  |
| -------------- | ------------------ | ------------------ | ---------------------- |
| name           | VARCHAR            | Confirmed          | Student name           |
| email          | VARCHAR            | Confirmed          | Student email          |
| phone          | VARCHAR            | Confirmed          | Student phone          |
| course         | VARCHAR            | Confirmed          | Student course         |
| admission_date | DATE               | Confirmed          | Student admission date |

### AI Recommendations

The following fields are recommended for normal database record management:

| Field      | Proposed Data Type   | Status            |
| ---------- | -------------------- | ----------------- |
| id         | BIGINT / INTEGER     | AI Recommendation |
| created_at | TIMESTAMP / DATETIME | AI Recommendation |
| updated_at | TIMESTAMP / DATETIME | AI Recommendation |

These fields are not additional user requirements. They are technical recommendations for identifying and maintaining database records.

---

# 4. Deletion Strategy

The project requires the ability to delete student records.

However, the exact deletion strategy has not been confirmed.

Therefore, two possible approaches are identified:

### Option A — Hard Delete

The student record is permanently removed from the `students` table.

**Status:** Open Decision

### Option B — Soft Delete

The student record remains in the database but is marked as deleted.

This approach would require an additional field such as:

`deleted_at`

**Status:** AI Recommendation / Open Decision

### Important

`deleted_at` is **not part of the confirmed baseline schema**.

It should only be added if the project later chooses the Soft Delete approach.

---

# 5. Proposed Baseline Schema

The following represents a **technical recommendation**, not a final database implementation.

```sql
CREATE TABLE students (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    course VARCHAR(100) NOT NULL,
    admission_date DATE NOT NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
);
```

### Schema Notes

* `id` is an AI-recommended record identifier.
* `created_at` and `updated_at` are AI-recommended record-management fields.
* No UNIQUE constraint is currently defined for `email`.
* `deleted_at` is intentionally not included in the baseline schema because the deletion strategy is still open.
* The exact data lengths and database-specific syntax may be adjusted after the database engine is selected.

---

# 6. Search and Filtering

The project requires student search and filtering.

However, the exact fields and behavior have not yet been confirmed.

Therefore, no specific search or filtering field should currently be treated as a final database implementation decision.

### AI Recommendations

Possible searchable fields may include:

* Name
* Email
* Phone

Possible filter fields may include:

* Course
* Admission Date

These are recommendations only and require confirmation before being treated as final implementation requirements.

### Combined Search and Filter

The final database queries will depend on the search and filter behavior confirmed during the Functional Logic stage.

No specific SQL query is considered final at this stage.

---

# 7. Database Operations

The database must support operations required by the confirmed student management functionality.

### Add Student

A new student record will be inserted into the student storage.

Conceptually:

```text
Student Form
    ↓
Validation
    ↓
Database INSERT
    ↓
Student Record
```

The exact validation and database implementation will be defined in the Functional Logic stage.

---

### View Students

The system will retrieve student records from the database and display them to the administrator.

The exact ordering, pagination and filtering behavior are not yet finalized.

---

### Edit Student

An existing student record will be updated with the modified student information.

Conceptually:

```text
Existing Student
    ↓
Edit Form
    ↓
Validation
    ↓
Database UPDATE
    ↓
Updated Student
```

---

### Delete Student

The system will provide the ability to delete a student record.

The actual implementation will depend on the deletion strategy selected:

* Hard Delete
* Soft Delete

This decision remains open.

---

# 8. Database Relationships

At the current project scope, the confirmed requirements do not require relationships with additional database tables.

Therefore, the baseline design contains only the student data structure.

### AI Recommendation — Courses Table

A separate `courses` table could be introduced in the future if the project requires:

* standardized courses
* reusable course records
* course management
* relationships between students and courses

This is an optional recommendation and is **not part of the current confirmed database design**.

For the current baseline, `course` can remain a simple field in the `students` table.

---

# 9. Admin Data and Authentication

The project requires an administrator who manages student records.

However, the authentication mechanism and storage of administrator accounts have not yet been confirmed.

Therefore, an `admin_users` table is **not included in the current database design**.

### AI Recommendation

If authentication is required, administrator accounts can be implemented using an appropriate user/account structure after the authentication approach is decided.

This decision should be handled during the Functional Logic and technical implementation stages.

---

# 10. Database Indexing

Database indexes can improve query performance, but the final indexes should depend on the confirmed search and filtering behavior and the selected database engine.

### AI Recommendations

Potential indexes may be considered for:

* `name`
* `email`
* `phone`
* `course`
* `admission_date`

However, these indexes should **not be treated as mandatory final indexes** until the actual search/filter requirements and database engine are confirmed.

No performance target is being introduced at this stage.

---

# 11. Database Engine

The database engine has not been confirmed in the previous project stages.

Possible options include:

* MySQL
* PostgreSQL
* SQLite

The final choice should consider:

* project simplicity
* development environment
* deployment environment
* application framework compatibility
* required database features

### Status

**Open Decision**

The SQL shown in this document is therefore illustrative and may require minor syntax changes depending on the selected database engine.

---

# 12. Open Questions

The following database-related decisions remain open:

1. Which database engine will be used?
2. Should student deletion use Hard Delete or Soft Delete?
3. Should email values be unique?
4. Should `course` remain a simple text field or use a separate `courses` table?
5. Which exact fields should support student search?
6. Which exact fields should support student filtering?
7. What authentication approach will be used for the administrator?
8. Should pagination be required for the student listing?

These questions should be resolved before the corresponding implementation decisions are finalized.

---

# 13. Database Design Decisions for Next Stage

The following items are currently considered **confirmed baseline data requirements**:

* Student name
* Student email
* Student phone
* Student course
* Student admission date
* Student CRUD capability
* Student search capability
* Student filtering capability

The following remain **AI Recommendations / Open Decisions**:

* Record ID
* Timestamps
* Email uniqueness
* Hard Delete vs Soft Delete
* `deleted_at`
* Search field selection
* Filter field selection
* Course normalization
* Database indexes
* Authentication database structure
* Database engine

No unconfirmed technical decision should be treated as final until it is approved.

---

# 14. Web Builder Workflow

The project follows the configured Web Builder workflow:

```text
Project Understanding
        ↓
Project Proposal
        ↓
Logical Roadmap
        ↓
Database Design
        ↓
Functional Logic
        ↓
Verification
        ↓
UI Design
        ↓
Coding Structure
```

The next stage after Database Design is:

**Functional Logic**

The Functional Logic stage will define how the confirmed requirements and approved database design are translated into application functionality, including forms, validation, actions, routes and workflows.

No additional project stage is introduced by this document.

---

# 15. Next Steps

1. Review this Database Design Document.
2. Resolve important open database decisions where necessary.
3. Approve the Database Design.
4. Move to **Functional Logic**.
5. Define the detailed working logic of the confirmed student management features.

---

## Final Status

**Database Design Status:** Under Review

This document intentionally separates:

* Confirmed Requirements
* AI Recommendations
* Open Decisions

so that unconfirmed technical choices are not accidentally treated as project requirements.
✓ Approved & Locked Cancel
Stage Information
Database Design

Project ke liye required database structure design karna.
Expected Output

Tables, fields, relationships, indexes and database logic.
Previous Stages
Project Understanding
✓ Available
Project Proposal
✓ Available
Logical Roadmap
✓ Available
AI Generation
Stage Ready
All required previous stages are approved.
✓ This document is approved and locked.
Rework with AI
✓ Document Approved
Rework is disabled because this document has been approved.