Page 1 of 1

To delete records which has 1-2-m relationship

Posted: Mon Apr 14, 2003 7:39 am
by minds_gifts
Hello everybody,

I need some advice with one of my tasks.Here it goes....

I've got 3 tables(Topics, Subtopics, Articles) which are related to each other.

Each Topic has many Subtopics and each Subtopic has got many articles.

I want to design a form inorder to delete either a topic or a subtopic or an article.

Well, I've written a piece of code in order to delete the articles and its working fine.

What happens if I directly delete the subtopics though there are some articles exsisting under them??and similary with the topics if there are some sub-topics under their belt.

If this is gonna happen, I think the system will not be a stable one.My articles table look like(Article_id, Subtopic_id, Topic_id, Article_name Article_file, Article_keyword)

How best can I do inorder to make the system stable so that I can be able to delete a topic or a sub-topic or an article.

Thanks in advance

Posted: Mon Apr 14, 2003 5:38 pm
by m3mn0n
If you say that they are related, in each related feild for, let's say articles. Add which subtopic it belongs to. So then when you delete a subtopic, add another SQL query that deletes all articles WHERE subtopic = 'whatever'. :) Hope it helps.

Posted: Mon Apr 14, 2003 6:30 pm
by minds_gifts
Hello Oromian,

Could you explain me further???

Many thanks

Posted: Mon Apr 14, 2003 6:35 pm
by m3mn0n

Code: Select all

DELETE * from articles WHERE subtopic_id='$subtopic_id';
When you delete a subtopic, run that query along with your article deletion one. This will then delete any related articles to that particular subtopic you are deleting. I hope this is what you mean't. :wink:

Posted: Mon Apr 14, 2003 6:51 pm
by minds_gifts
Not really though!
OK, I've got another idea.

First, I'll display all the topic names and next to each topic name there'll be a check box.Once, when the user checks this check box and hit the button delete, the record is deleted.But, before the records get deleted, is it possible to make a "LOOK UP" on the sub-topics table so that if there are sub-topics related to this topic_id then the record should not be deleted instead it has to show some error mesg.
Is that possible, if so, could somebody please give me a start up.

Table structure:
1.Topics(topic_id, topic_name)
2.Subtopics(subtopic_id, topic_id, subtopic_name)

Many thanks