Database design problem

Sachin Singh

5y983

i am developing an CMS for teachers , where they can login, design thier course and then post articles which will be save on SQL Server database.

1.Teacher Logs in

2.He choose a Category (Let say Web Development)

3.Then he selects a Subcategory (say .. Javascript)

4.then he enters chapters in Javascript he want to teach.

5.then he enters Topics of each chapters.

6.Some Topics may have subtopics.

7.Finally he writes post on a particular subtopic/Topic of a chapter for a course under subcategory of category.

my question is about the structure of Post table

PostId

Title

Body

TopicId

ParentTopicId /// it indicates i am not reating separate table for sub topic

PostId

Title

Body

TopicId

SubTopicId /// it indicates i am creating separate Table for subtopic

which one is good for faster lookup and maintanance or any better solution.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Gupta5y ago
Accepted answer
Over the time, as we learn something new for improvements. In the faster growing digital era our code which was written 6 months back needs improvements. That's the reason Normalization (you might already aware) came into practice.

As we create table for user and its roles seperately and check on a seperate tables UserRoles for the authorization, in the same way :
Create a post in a seprate table that only belongs for a specific post related stuffs.
Create a seperate table for Topic/SubTopic/Category & so on..
Now all the above has its individual identity, Create a table NewPost then use the ID of the parent table into it.
Have a look to the table architecture for almost same category alike
https://www.brafton.com/support/simple-database-tutorial/
Sachin Singh5y ago
@Amit Sir
Just one more clearification , sir how to design Bridge table then when i have both topic and subtopic

(PostId TopicId SubTopicId) --->then here sub topic Id will be null for many posts or

(PostId ,TopicId )+(PostId,SubTopicId ) ---> two bridge tables
Sachin Singh5y ago
aman i have used the same , meaning a separate table for sub topic , but is it really the best means , many SQL leaders have warned me to not design like this .
Aman Gupta5y ago
Hi Sachin,

While designing the table you should use a separate table for the Subtopic.
Assuming taking parent category as Java with its Id as primary key and using that same Primary key as Foreign key in the separate table for its subtopic as Collection, Multithreading, Opps Concepts, etc.

It will help in searching for articles easily and maintaining the parent-child hierarchy.