Ideal way to represent choices with remarks in database

Question

I have no way to describe my dilemma but say I have to "grade" a topic with grades A, B, C and I need to provide remarks for each.

Each topic is divided into X categories with Y topics that needs a "grade" and remarks.

| Topic 1    | A | B | C | Remarks |
|------------|---|---|---|---------|
| Subtopic A |   |   |   |         |
| Subtopic B |   |   |   |         |
|------------|---|---|---|---------|
| Topic 2    | A | B | C | Remarks |
|------------|---|---|---|---------|
| Subtopic A |   |   |   |         |
| Subtopic B |   |   |   |         |
| Subtopic C |   |   |   |         |
| Subtopic D |   |   |   |         |

Scope:

• all subtopics are optional
• if a subtopic grade is answered, then a remark is required

Limitations:

• I am limited to a non-Barracuda MySQL environment and I plan to allocate 2048 bytes for each remark

---

(SQL pseudocodes only, do not mind if they are faulty)

Plan A:

Just do a table for each topic storing the subtopic name, its grade and remarks i.e.

TABLE topic_1 (
    id AUTO_INCREMENT UNIQUE PRIMARY KEY,
    subtopic_a ENUM('A', 'B', 'C') NULL,
    remarks_subtopic_a VARCHAR(2048) NULL,
    subtopic_b ENUM('A', 'B', 'C') NULL,
    remarks_subtopic_a VARCHAR(2048) NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8;

but this is terrible since the grades are not declared in one place.

Plan B:

Create table for all subtopics, their names hardcoded but references the grades declared in another table. All remarks are distributed to other tables by topic and resource limitations:

INSERT INTO grades (grade)
VALUES ("A"), ("B"), ("C"); 

TABLE topics (
    id AUTO_INCREMENT UNIQUE PRIMARY KEY,
    topic_1_subtopic_a INT UNSIGNED NULL,
    FOREIGN KEY (topic_1_subtopic_a)
        REFERENCES grades(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    topic_1_subtopic_b INT UNSIGNED NULL,
    FOREIGN KEY (topic_1_subtopic_a)
        REFERENCES grades(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    topic_2_subtopic_a INT UNSIGNED NULL,
    FOREIGN KEY (topic_1_subtopic_a)
        REFERENCES grades(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    ...

) ENGINE=INNODB DEFAULT CHARSET=utf8;

TABLE remarks_topic_1 (
    id AUTO_INCREMENT UNIQUE PRIMARY KEY,
    subtopic_a VARCHAR(2048) NULL, 
    subtopic_b VARCHAR(2048) NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8;

TABLE remarks_topic_2 (
    id AUTO_INCREMENT UNIQUE PRIMARY KEY,
    subtopic_a VARCHAR(2048) NULL, 
    subtopic_b VARCHAR(2048) NULL
    subtopic_c VARCHAR(2048) NULL, 
    subtopic_d VARCHAR(2048) NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8;

Plan C:

Since we have a pattern of a subtopic to have the following attributes:

• supertopic
• grade
• remarks

Make a table for this wherein the contract of the number of topics and their respective subtopics are only "assembled" and known in the front-end; in the back-end they are all only seen as subtopic database objects:

INSERT INTO grades (grade)
VALUES ("A"), ("B"), ("C"); 

TABLE subtopics (
    id AUTO_INCREMENT UNIQUE PRIMARY KEY,
    topic VARCHAR(100) NULL,
    subtopic INT UNSIGNED NULL,
    FOREIGN KEY (grades)
        REFERENCES grades(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    remarks VARCHAR(2048) NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8;

PROs:

• generalized patterns
• grades are defined in the database
• not difficult to store one completed subtopic given the remarks

CONs:

• front-end only knows the contract/business logic
• topics and subtopics are not hardcoded

---

Is Plan C a good way to start? Or is there a better way to deal with this?

Answer 1

Yes, the plan C is the better design because it is more normalised and scalable. That is the proffered way of modelling RDBMS. You may also externalise "Topic" column to a separate table or can have a parent child relationship in the same table so that you can manage the topics easier. Otherwise topic will be duplicated among all subtopics and you might have to update all the rows if there is any change in future.

All other mentioned approaches have some scalability and maintainability issues.