Jump to content
Larry Ullman's Book Forums

Delete Rows From Two (Or More) Tables


Recommended Posts

In chapter 5 you are talking about deleting data and the example to delete a record is:

"DELETE FROM users WHERE user_id = 8 LIMIT 1"

 

You never get to talk what happens with the posts of this user from the table "messages".

 

I assume the query to delete the user should reflect this situation as there may be situations (unlike a forum) where we want to delete everything related to that user.

 

Should we create two separate queries in order to verify the actions (hence the "LIMIT 1") or we pack everything in one query, I assume something like:

 

"(DELETE FROM users WHERE user_id = 8 LIMIT 1) AND (DELETE FROM users WHERE fkuser_id=8)"?

 

What will be the best way to do it considering factors like we may want to verify the action was performed succesfully or not and we may use prepared statements or not.

Link to comment
Share on other sites

Good question. The queries in that chapter are mostly demonstrative, but you're right that you would want to watch that you don't leave "holes". Foreign key constraints can guarantee that or you can use transactions. I don't believe you can do a delete across two tables like that.

 

All that being said, I try to design my systems to never delete any data from the databases. It's safer that way, in the long run.

Link to comment
Share on other sites

i usually keep a separate page with the delete actions (delete user, delete posts of that user) for a specific task (delete user from forum) and simply run two separate queries, the second based on the success of first. This way I avoid using transactions...

It seems simple, not sure if it is the best thing what can be done...

I understand the transactions have a downside. How about database triggers?

At the end of the day probably all perform the task and it is not such a big deal (or it is)?

Link to comment
Share on other sites

You said that the main drawback would be that you cannot use MyISAM tables.

From what I understand from your book these are faster than innoDB.

I am thinking that for an update I would probably prefer transactions from the way you explain it (the example with the money transaction). But for a straight forward delete it looks easier to simply run the queries. Not elegant, maybe a little more time consumin as it has to run multiple queries...

 

In my particular situation in my application deleting records is not a frequent operation, I will say very rare in the form of deleting everything from multiple tables with reference to an id. (Like on this blog, chances are people will not delete their profile with posts and everything.) Instead a search is a very often operation and there are thousands of records. Under these circumstances will MyISAM table compensate for the brute use of several queries to delete records instead of a single transaction? The downside will be to change the table type to slower innoDB. What do you advise? Thank you.

Link to comment
Share on other sites

Well, the speed differences between MyISAM and InnoDB varies from version of MySQL to version and OS to OS.

 

As for what I advise, I advise never to delete records, as I said earlier. If that's not in your plans, then just choose the table type that's appropriate in general and code around the possibilities. You wouldn't want to decide your table type for this minor, hopefully uncommon, need.

Link to comment
Share on other sites

I am working on a job site and there are thousands of records that will be searched. When I chosed the table type I had this in mind. I tend to keep the records as you said, however in some circumstances it will not make sense. In my example when an user is creating his/her cv one will have to add a series of information like "from this period of time to this period of time I worked here and did this". If one of this lines is wrong it makes sense to allow the user to delete it. I will not delete the user account or other things, but in this particular case the information is of no use (it is simply wrong) and it makes sense to delete it. I am sorry I had to enter in details, I try to keep the posts as general as I can to be of use for anybody else. So question become something like: in cases like this one where the delete action is the reasonable approach, but it has just a minor role at the application level, should I change to using transactions and InnoDB instead myISAM and "if... else..."? From what you are saying - "code around the possibilities" - I should maintain MyISAM tables as the application will operate better/faster with them. Code around is an "if... else...". May I generalize and say that when we are having lots of SELECT queries is generally better to use MyISAM tables and "code around" the sections that may make use of transactions?

 

I would like to understand better his issue. How should we think when decide pro or against transactions in a particular case. I have in mind the following: on the registration process as per your book, we check to see if the email is in the database, if it's not in the database than we enter the details collected from form in the database. Why not using transactions here?

 

Another example: a table with USERS and a table with USER DETAILS. I add information to USER DETAILS table and I need to update a column in the USER tables that keep track of something like "user status" (it may be "user level" or "user progress" for a registration that require several steps). I can use transactions here, but I don't see in which way it is different than the registration example. There are two tables that I am working upon, not one as in the registration, but the logic is the same. How are these different? What is your thinking process when you decide what to use?

 

Generally speaking, how do you decide if you need transactions instead of of an "if... else" (as in the registration process: if one row is affected we know the information was added to the database so we move to run another query, in the registration process we add information to database, in my example we updated the status of the user in another table).

 

Las thing: how do we check if a transaction was succesfull or not and why don't we check the success of the second query in something like the registration process? In the registration example we know the first action happend by checking affected rows, we don't know for sure the second action happened but we assume that it was performed with success. Shouldn't be checked if the second operation happened? If we don't check for the second operation, why do we check for the first operation? Obviously in the registration process it should be checked and it depends upon the result of the first query, but in my example there are two input queries, why don't I simply assume that both queries performed succesfully? As in an delete situation. If I delete everything related to this user from this table I delete everything related to this user from this other table as well. Why and when to check if one, both or none of the queries happened?

Link to comment
Share on other sites

May I generalize and say that when we are having lots of SELECT queries is generally better to use MyISAM tables and "code around" the sections that may make use of transactions?

 

It really depends upon the version of MySQL in use and the OS, neither of which have you yet mentioned. The only way to be certain what will perform best is to run actual benchmarks on the actual server. But if the primary query type being used is SELECT, then the table choice should be what's best for those queries.

 

I have in mind the following: on the registration process as per your book, we check to see if the email is in the database, if it's not in the database than we enter the details collected from form in the database. Why not using transactions here?

 

 

Because it makes no sense to. While there are steps here, they aren't truly dependent upon another. For example, while the running of the second query depends upon the results of the first, the first doesn't actually make any changes. Thus, if the second query fails to run successfully, you don't have to undo anything from a previous query.

 

 

Another example: a table with USERS and a table with USER DETAILS. I add information to USER DETAILS table and I need to update a column in the USER tables that keep track of something like "user status" (it may be "user level" or "user progress" for a registration that require several steps). I can use transactions here, but I don't see in which way it is different than the registration example. There are two tables that I am working upon, not one as in the registration, but the logic is the same. How are these different? What is your thinking process when you decide what to use?

 

It's very much different than a registration example in that two CHANGING queries are begin run. The registration example is a SELECT followed by an INSERT. Failure of the INSERT would not mandate an undoing of the SELECT> But this particular example would also sound like a potential for a bad database design.

 

Generally speaking, how do you decide if you need transactions instead of of an "if... else" (as in the registration process: if one row is affected we know the information was added to the database so we move to run another query, in the registration process we add information to database, in my example we updated the status of the user in another table).

 

Again, the registration example doesn't make sense at all. But the rule is simple: you use transactions where the failure of a subsequent query would lead to inaccurate data if previous queries were not revoked. The banking example is clear in this regard: if I try to transfer money from one account to another, both accounts need to be updated or the entire transaction should be revoked.

 

Las thing: how do we check if a transaction was succesfull or not and why don't we check the success of the second query in something like the registration process? In the registration example we know the first action happend by checking affected rows, we don't know for sure the second action happened but we assume that it was performed with success. Shouldn't be checked if the second operation happened? If we don't check for the second operation, why do we check for the first operation? Obviously in the registration process it should be checked and it depends upon the result of the first query, but in my example there are two input queries, why don't I simply assume that both queries performed succesfully? As in an delete situation. If I delete everything related to this user from this table I delete everything related to this user from this other table as well. Why and when to check if one, both or none of the queries happened?

 

Well, the registration example is illogical for transactions. Other than that, you validate the results of each query in a transaction as the queries are executed. If the query worked, you continue the transaction (and commit it once it's done). If a query fails, you terminate the transaction. Whether you're using transactions or not, you should always check the success of a query's execution.

Link to comment
Share on other sites

Thank you, I think I understand: "transactions should be used when the failure of a subsequent query would lead to inaccurate data if previous queries were not revoked".

 

You said the particular example I gave "sound like a potential for a bad database design." I basically have the following design: an user table that holds the login information with a column ("level") that records the progress of the completion of all the other tables. So the application starts with level = 0 (default) for unregister and will increase to 1 if the second table is filled, 2 if the third table is filled and so on.

 

If the design is wrong, how should I improved it?

 

On completion of every step (filling a new table) the "level" field will have to be updated in the first table. Is this the case of using transactions?

 

tablesh.png

 

 

Regarding the version of MySQL in use and the OS I do not have one selected, I will buy hosting services after the application is ready and I can accommodate any suggestion. I guess the question becomes what version of MySQL and OS is recommended when using InnoDB tables and what is recommended when using MyISAM? Does the fact that it will use utf-8 influence the decision?

 

Thank you for your help.

Link to comment
Share on other sites

You said the particular example I gave "sound like a potential for a bad database design." I basically have the following design: an user table that holds the login information with a column ("level") that records the progress of the completion of all the other tables. So the application starts with level = 0 (default) for unregister and will increase to 1 if the second table is filled, 2 if the third table is filled and so on.

 

If the design is wrong, how should I improved it?

 

Well, I still don't understand the specifics and the fruits/vegetables example is rather odd, so I'll trust that the design makes sense. Or, given the information provided, I couldn't suggest a different design.

 

On completion of every step (filling a new table) the "level" field will have to be updated in the first table. Is this the case of using transactions?

 

Yes. Changes made by one query need to also cause changes made by another query.

 

Regarding the version of MySQL in use and the OS I do not have one selected, I will buy hosting services after the application is ready and I can accommodate any suggestion. I guess the question becomes what version of MySQL and OS is recommended when using InnoDB tables and what is recommended when using MyISAM? Does the fact that it will use utf-8 influence the decision?

 

You would not choose your server OS based upon the database storage engine. There are too many, way more important, server factors than that. UTF-8 will not be an issue either way.

Link to comment
Share on other sites

I will reply backwards. I am not aware of what server factors are important, I guess this goes beyond what your book covers. If you can offer some general guidance will be appreciated, otherwise I understand that your answer should be bind to specifics and it is not that I am unwilling to offer them (because I am), but I do not want to abuse your kindess and get into so much detail that the thread will be of no use for other users.

 

Regarding the example I gave I put fruits and vegetables as it makes no difference what the rest of the columns contain. The user should fill several tables with information and we need a column to keep track where he/she is: "level". Now you said this design may potential be wrong. Is it because the level column is in the users column? Should I make a table only to keep track the progress?

 

x862inwg326mv4km4bsx.png

Link to comment
Share on other sites

My concern about your database design came from what sounded like storing the same information in more than one table, which would be bad. But you're talking about storing an amalgamation of other table data in one column. That's technically redundant but an appropriate solution in many cases.

 

As for the server factors, I feel like we've gotten totally off topic. What is the question that still remains unanswered?

Link to comment
Share on other sites

The information is relevant only to that particular table, this is why I use a new table. I can work with each individual table at a time or with all, in which case I create a view where I join all the tables together (performing some other tasks as well, as computing the "age" from "date of born" and similar). The information it is not redundant, nor an amalgamation. First table is the user, second is profile, third is education, fourth is experience - it is a (part of a) database for a job web site. The question is if I have to take out the "level" column from "users" table and create a new table only to keep track of the progress.

 

Another question may relate to use of views, but I guess I will start a new thread for relevance.

 

Otherwise I agree with you and I may say that no stone was left unturn. Thank you for your time.

Link to comment
Share on other sites

 Share

×
×
  • Create New...