0

I have below tables while adding the product

  1. product_table [contains basic details like pid, name, rate, height, width]
  2. product_sellers [contains param like seller details]
  3. product_offers [contains offers on products]

and so on 5 tables. There are total 8 tables and all the tables are linked through pid as foreign key.

I know below three approaches:

  • Using Insert statement one by one on all tables

    $sql = "table 1 insert"; $sq2 = "table 2 insert";
  • make functions of all tables and call them one by one.

  • Using BEGIN and COMMIT statements.

    BEGIN
  $sql = "table 1 insert";
  $sq2 = "table 2 insert";
COMMIT

Is there any better approach and out of above 3 which approach is faster and proficient?

Gags
  • 3,759
  • 8
  • 49
  • 96
  • Your second approach: what exactly do you mean by "make functions of all tables"? – jbafford Jan 11 '16 at 15:10
  • means make functions which has insert statements and call them by passing last_insert_id as a fuunction – Gags Jan 11 '16 at 15:10
  • check this SO question: http://stackoverflow.com/questions/5178697/mysql-insert-into-multiple-tables-database-normalization – Mr Heart Jan 11 '16 at 15:13

1 Answers1

1

The advantage for using

    BEGIN
      $sql = "table 1 insert";
      $sq2 = "table 2 insert";
    COMMIT

is that your changes can be batched together on the server side.

Because you are making changes to multiple tables, putting all of the changes into a transaction (rather than running in autocommit) will allow you to back out of all of the changes if one of them fails. It will also allow the changes to your database to appear atomically to other clients, rather than have changes become visible as each query executes.

It is less of a factor in this case, but when multiple modifications to the same table are batched together, that may also allow for more efficient updating of indexes.


How you implement that in your PHP code is up to you. Depending on the needs of your application, one function that handles everything may be fine. But it may also be better to split the individual insert calls to the database into different functions that can be reused (especially if pieces of the product could change after being created), and have one function that handles "insert a product" by starting the transaction, calling the individual functions, handling errors, and committing or rolling back the transaction as appropriate.

jbafford
  • 5,528
  • 1
  • 24
  • 37
  • If i am taking function approach then i do not think that it can be reused anywhere in application. In this transaction how i can do error handle if any of Insert statement fails – Gags Jan 11 '16 at 16:07
  • What you do for error handling is entirely up to you; without knowing more about your application, it's not really possible to advise in specific. Generally, your error handler would probably execute a `ROLLBACK` query to revert the changes that had already been started so the database is in a clean state, and return some kind of error so that your application can handle an insert failure in an appropriate manner. – jbafford Jan 11 '16 at 16:10
  • How about using mysqli_multi_query – Gags Jan 11 '16 at 16:12
  • mysqli_multi_query lets you execute multiple queries in one call to the database, but it's still basically the same as making multiple individual mysqli_query calls. It's just a performance optimization to reduce the number of round-trips to the server (but without the ability to do anything as a result of a failure of an individual query until they've all executed). – jbafford Jan 11 '16 at 16:15
  • Yup. I got that. From above comments it feels that Begin Commit approach is best to use. Can you do me a favor by creating one transaction with Error Handling for just 2 queries as i m not quite confident in using Transactions. U cAn use http://sqlfiddle.com – Gags Jan 11 '16 at 16:20