Updating content on interrelated tables

Best Practice?

[lorax]

I have 3 tables all joined.

Table 1 deals with issues.

pk => issue_id

Table 2 deals with actions to address those issues.

pk = action_id

Table 3 is the reference table that glues Table 1 and Table 2 together.

issue_id ¦ action_id
--------------------
1 ¦ 12
1 ¦ 13
1 ¦ 21
2 ¦ 5

You get the idea, one issue, multiple actions (specific number unknown until the issue is created).

Inserting the records works great. Now I'm working out the editing/updating part. I'm wondering if I can use a Join and effectively update the appropriate records in Tables 1 & 2 at the same time - or if I need to use seperate queries.

Or is there a better way to do this?

[BitBanger]

Well, I would use two tables rather than three. The 'issue' table remains the same as yours, but combine the 'actions' and link table together by adding the link to the 'issue' table into the 'actions' table.

This way you have only two tables to deal to keep in sync, and in fact only one of them (actions) needs to be modified in most cases.

As far as your main question, I don't know of a way to update two tables in one query. With the above table design though, it really shouldn't be necessary.

(The two table solution assumes that you have a one-to-many linkage between tables, not a many-to-many linkage)

Hope this helps.

[lorax]

Thanks BitBanger,
I wondered about using two instead of three. Perhaps I over normalized this application.

[ergophobe]

Perhaps I over normalized this application.

Advanced apologies: I'm not trying to be pedantic, but do have a point here if you'll bear with me!

I don't think it's possible to "over normalize" in the sense that you mean it. A fully normalized table will have the absolute minimum repetition and every column in a row must be directly dependent on the primary key. If adding a table increases repetition, you are denormalizing, not overnormalizing.

I don't mean this to be purely about terminology, but I'm trying to give a rule of thumb and a way to think about the "how many tables" question.

If banger is right and it is a one-to-many relationship, than you are unnecessarily repeating data with three tables, and therefore denormalizing. The key question is:

- would "actions.issues-id" be directly dependent on one and only one "actions.actions-id"?

If the answer is yes (one-to-many), to put it anywhere but the actions table will be short of fully normal (not overly normal).

If the answer is no (i.e. many-to-many relationship), to put it in the actions table will be short of fully normal.

As for the original question, I think best practices in terms of updating multiple tables in a single query would be to use transactions, so you have an all or nothing situation (either the whole update succeeds or fails, but you don't end up with one table updated and not the other). I don't think that really applies here, because as banger says, once you do the original insert of the "issue", you won't be updating that every time you declare a new action.

There is a recent set of articles on MySQL Transactions at Devshed about basic [devshed.com] (november 2003) and more advanced [devshed.com] (december 2003) topics relating to transactions. I'm sure you'll find tons more if you Google.

Of course, older version of MySQL do not support transactions. In that case, if you really want to ensure data integrity, you need to do some sort of iterative checking as in:

I. update table 1.

II. check for success
1. Failure - quit - send error message: update failed, try again.
2. success - go on to step III

III. update table 2

IV. check for success
1. Success - yeah! quit
2. Failure - roll back update from step I. Go on the step V.

V. verify successful rollback of I.
1. Success - quit. Send error message - update failed, data not compromised
2. Failure. Try again. On second failure, send error message: update failed, data may be compromised.

Somethign like that.

Tom

[lorax]

Thanks Tom,

>> - would "actions.issues-id" be directly dependent on one and only one "actions.actions-id"?

Most likely but not guaranteed which is why I chose to go with 3.

It's a non-profit I'm dealing with. And as par for the course, they are all over the place when it comes to using technology. Most of them want to edit this stuff as if it were a word processor and/or paste-up tool. The result is a collection of mixed voices on a single website. SO part of my job here is to streamline the way they do their updates (read: box them in a bit).

As the code stands now, it could easily use and benefit from the 2 table structure. The admin tools I built for them don't allow them to mix and match actions with issues at will. That was to be a future addition - once they've settled down into a rythym. I suppose I could just as easily return to the 3 table structure with a simple script then.

[ergophobe]

By the way, two tables or three, the bit about transactions and whatnot should be true.

Cheers

Tom

[coopster]

I'm wondering if I can use a Join and effectively update the appropriate records in Tables 1 & 2 at the same time - or if I need to use seperate queries.

It really depends on the database and version of that database. More often than not, we see MySQL on these boards, therefore I'll use it to further describe my response. If the database supports stored procedures and triggers [mysql.com], etc. you could perform the operation in one query. However, I'm willing to bet you are seeking an approach that doesn't utilize stored procedures...

I haven't tested it yet, but starting with MySQL Version 4.0.4 you can perform UPDATE operations that cover multiple tables:

UPDATE items,month SET items.price=month.price 
 WHERE items.id=month.id; 

The example shows an inner join using the comma operator, but multiple-table UPDATE statements can use any type of join allowed in SELECT statements, such as LEFT JOIN. Now, does this mean you could actually update columns in both tables as well? Something like...

UPDATE table1,table2 SET table1.field1='$my_value',  
 table2.field1='$my_other_value' 
 WHERE table1.key=table2.key; 

I don't know. You would have to test. I don't have a MySQL > 4 server set up (yet).

[lorax]

Thanks coopster,
Yes, it is a MySQL db. No, I'm not using stored procedures - probably not even availabe as this is a shared server. I wonder if transactional processing might be a suitable replacement. I've never had to work with transactional code so I'm a bit in the dark with whether or not it's a good fit but the concept seems to be similar to what you're suggesting (e.g. the commit/rollback ability) but I don't know if it would be an appropriate solution.

[lorax]

Update:

What I ended up doing was to get rid of the extra table.

For the updating I had to find a way around the fact that the # of actions is not known until the edit form performs it's query to the db for all of the actions associated with the given issue. So I created a var that passes the ids for these as a string in a hidden field.

Then in the form handler I run two queries using the suggestion from ergophobe - i.e. run the main query and test for success then run the second query (actually a looped query controlled by the number of actions) like so:

if($result!= FALSE) {
sort($action);
$ids = explode(",",$action_ids);
for($i=0; $i<count($ids); $i++) {
$query = "Update actions
Set action_copy = '$action[$i]'
Where issue_id = $issue_id
AND action_id = $ids[$i]";
$result = mysql_query($query) or die("Edit Query failed: ".mysql_error());
}
}

If the first query fails, the actions won't be updated and I'll get an email notifying me of the error. If it succeeds, the update proceeds.

Thoughts: this code won't see a lot of use. A couple of times a month maybe. I don't like the idea of the multiple queries but don't see an alternative given that the # of actions is more than one and can vary.

I also think I'm going to look into the transactional processing option just to see if it's possible.

[coopster]

Update:

For the record, I can confirm that it is possible to UPDATE columns in both tables as described and questioned in msg #7 using MySQL 4.1. I can only assume that it would also work with MySQL 4.0.4.