SQL Insert in an AJAX form

Question

In a database where there is a many to many relationship (say 3 tables like this for example)

Book    |   Author    |    BookAuthor
_____   |   ______    |    __________
BookId  |   AuthorId  |    BookId
Title   |   Name      |    AuthorId
...     |   ...       |

I want a form to create a new book. In that form, the users would enter the information about the book (Title, etc.) and they would select the authors for the book, all through the same form.

In a situation like that, when do you do the insert in the Book table? Do you do it when :

1. you access the form, so that you get the BookId and that you can insert in the BookAuthor table as the user adds the authors?

2. do it only when the user leaves the form (you insert in Book and then in BookAuthor)?

With 1, you could end up with a bunch of unwanted records, as a record is created even when the users leave the page or hit cancel.

With 2, you don't have the BookId, so you can't start inserting in the BookAuthor table until the whole form is filled (you can't really use AJAX then).

This seems like it would be a fairly common scenario, so I imagine that there is a correct way of doing this?

EDIT : The authors could be added like the tags are added to a question on Stack Overflow (auto-complete box) like it was suggested in an answer. You add a tag, but it can't be inserted right away because the question doesn't exist in the database yet. So when you submit your question, I'm guessing that the content of the auto-complete box is parsed and that both (tags and question) are inserted at the same time. I don't know how it's done on SO (parsing or something else) but that's basically what i'm trying to find out.

Answer 1

Your AJAX call would hit a page that inserts into the Book table then inserts into your BookAuthor table. Both of these queries would be done within a transaction to maintain the integrity of the Primary/Foreign key relationship.

Depending on your database the BookID from your Book table can be got in many different ways. Either you can query for it after the Book table insert.

Select Max(BookID) as TheBookID from Book

Or if you are using Oracle you might increment a sequence first, before inserting into the Book table.

Select BookID_SEQ.nextval from dual

Then you could use this sequence number in the Book table insert as well as the BookAuthor insert.

All of these queries must be called within a transaction though so some sneaky bugger doesn't sneak in behind you and steal your Max(BookID) or increment your sequence.

Answer 2

I would definitely not suggest #1, for the reason you stated, and just doesn't make logical sense.

I'd suggest #2, or an evolution of #2.

First, I wonder why you want/need to do this with AJAX - contrary to your final statement, it's certainly possible -- but your simplified example would work with just as simple HTML form submit also.

Anyway, it depends on how authors are created as well. Do you want to allow the user to create author entries at the same time, or in addition to this? (I assume the latter).

If I were doing this project, I'd probably want to allow the user to create book entries and select from an auto-complete author box. I'd also want to allow them to create any new author entries from that box. For instance, if they enter an author name that isn't already in the author table, the system would create a new entry.

In the case where an author entry doesn't already exist, you'll need to create a new one, which will get you a new author ID.

After you've obtained your author ID (either from a lookup on existing, or creating a new one), you can create your book entry. After you've done that, you'll have the book ID, at which point you can add the reference in BookAuthor.

You probably want to wrap this all in a transaction, then commit after the BookAuthor entry is created.

You may want to use auto-increment columns for your ID's, and you probably want to use foreign key constraints for your ID columns when linking to the BookAuthor table.

This all aside, you can use AJAX for any of the client to server communication - that's just a way to communicate with the server from the browser.