I am developing a site of E-learning. I am facing a problem. The problem is like this. I have a main category like for example
1 Courses offered by University of XYZ
3 Courses offered by University of ABC
now category 1 has sub categories like
4 Master degree courses
5 Bachelors degree courses
6 Diploma courses
now category 4 again has sub categories like
7 Master in Computer Science
8 Master Computer Management
9 Master of Business Administration
(this level can go upto nth level)
now for any category there are courses attached to it lets say for category 7 3 courses are attached and they are
(here I would like to mention one thing that once a course is attached to any particular category then the category can not be divided further)
now for every course there subjects are attached lets say for crs1 3 subjects are attached.
now for each subject user can assign tutorials, assignment etc....
My problem is If I want to delete main category then I want to delete all the related sub-categories, courses, subjects, assignments, tutorials attached to it, which I am not able to do.
I am using MySQL dabase and front-end is in ASP.
MySQL 4.0.12-nt running on localhost.
I am using single table to store categories by assigning parentcategoryId to any particular category, and for courses I have categoryId as one field and for subject table I have courseId as one field.
Can anybody help me in this regard. I hope I am very much clear in
posting the problem.
Yes I have added constraints foreign key and on delete cascade. One problem got solved, but can anybody tell me the logic of finding child category and then finding child category of child category and so on....
because the levels can go to any depth
Due to foreign key and cascade delete I can delete course, related subjects and related assignment, tutorials for that particular category,
CatId CatDesc parentCatId
1 First Cat 0
2 Second Cat 0
3 Third Cat 0
4 Forth Cat 1
5 Fifth Cat 4
6 Sixth Cat 5
7 Seventh Cat 6
and the chain can go to any level... ok I can find out for particular category what are child categories by selecting * from tbCat where parentCategory=category
Lets say I got 3 child categoried, now I need to find out for each and every child category whether it is having a child category or not and so on....