UPDATE or DELETE first then INSERT

Question

I'm asking this again, because I can't get a straight answer from other questions on SO.

My situation is as follows, two tables, phones and features.

Structure:

phones

• id
• brand_id
• model
• picture
• published

features

• id
• phone_id
• feature

Now, features contains a whole lot of values, which only between 1 & 4 will correspond to a certain phone via phone_id.

Now, when I update features, do you think deleting and then inserting new features again or updating existing features will be best?

// edit:

specials

• id*
• phone_id
• contract_id
• start_publish
• finish_publish
• published

contract

• id*
• name
• benefit
• duration
• price, etc.

brand

• id
• name
• logo

So basically, a special links a phone and a contract together, and grabs info from both tables for output. Then during that process, each phone's features get loaded separately using a function.

// edit 2:

Oh, and only one phone's features will be deleted/updated at a time, and also, only between 1-4 features will be deleted at a time. Also, features can always be more or less, and never a set number.

Answer 1

If the number of features per phone will ever change, then you must delete and insert. To update, you'd need to:

• get the current set of features
• figure out what is different, what was added, and what was removed
• run queries to update, insert new data, and delete old data.

Where as if you just delete the whole list and re-insert them, you save yourself some maintenance headaches.

If consistency of your data is an issue or concern (the data is deleted but an error happens before insert causing the script to not complete) then wrap your queries in a transaction.

Take this example:

I have a blog with Posts(id, title, body) and Categories(id, post_id, category). My first Post, "Hello World" is categorized under "meta, php, and javascript". Then a year later, I revise the post to have some information about mysql (or I add a mysql category) and take out javascript.

Here's what I'd need to do (mostly psuedocode):

//old set
SELECT * FROM Categories WHERE post_id=1

foreach($categories as $category) {
    if(in_array($category['id'], $_POST['categories']) {
        //this category is in the new set and the old set, do nothing
    }
    elseif(!in_array($category['id'], $_POST['categories']) {
        //this category is in the old set, but not the new one, delete it
        $queries[] = "DELETE FROM Categories WHERE id=".$category['id'];
    }
    else {
        //new category, add it
        $queries[] = "INSERT INTO Categories()..."
    }
}

foreach($queries as $item) {
    //run query
}

Versus this:

DELETE FROM Categories WHERE post_id=$_POST['id']

foreach($_POST['categories'] as $item) {
    INSERT INTO Categories() ...
}

Personally, I think option #2 is better.

Answer 2

UPDATE should be used. Alternatively, deleting and inserting can be done in one REPLACE command.

http://dev.mysql.com/doc/refman/5.0/en/replace.html

Also, if features can be shared (many to many relationship), you may want to consider a third table that only links phone ids to feature ids.

Answer 3

When you "update features" you do just that, update.