Chain modifications in relational database

[silvercover]

Suppose that we have 3 related table in this manner in MySQL:

 

Table 1:

-------------

UserID

-------------

Username

-------------

Password

-------------

 

Table 2:

-------------

ProfileID

-------------

UserID

-------------

UserAvatar

-------------

 

Table 3:

-------------

ArticleID

-------------

UserID

-------------

Article

-------------

 

 

I need to delete every related records upon delete action on Table1. in other words, when user performs delete action on Table 1, related records from other related tables must be deleted automatically and not write tons of queries to achieve this or we have orphan records without its master record.

 

what should I do?

what are the best practices?

 

Thanks.

[Muddy_Funster]

I would suggest looking into "foriegn keys" and "cascade indexing" to achieve what you are looking for.

[mikosiko]

only if your storage engine is Innodb:

- Define Foreign KEY in table 2 and 3 to table 1 and use  ON DELETE CASCADE

 

 

[The Little Guy]

I would make a function and/or class, then call the function and/or class.

 

<?php
require_once 'link/to/db.php';
// if you don't connect to the db above, do it here
class db_procedures{
private $user_id = null;
public function __construct($user_id){
	$this->user_id = (int)$user_id;
}
public function delete_records(){
	$this->delete_from_1();
	$this->delete_from_2();
	$this->delete_from_3();
}
public function delete_from_1(){
	mysql_query("delete from table1 where UserID = $this->user_id");
}
public function delete_from_2(){
	mysql_query("delete from table2 where UserID = $this->user_id");
}
public function delete_from_3(){
	mysql_query("delete from table3 where UserID = $this->user_id");
}
}


$dbp = new db_procedures(12345); // Create instance of class and pass user id "12345"
$dbp->delete_records(); // Deletes records from table 1, 2, 3

// Or to delete just from table 2:
$dbp->delete_from_2();
?>

 

edit:

hmm... looks like may have I misunderstood the question, but the code above may help someone..