[ThunderPoit]

I've been wracking my brain trying to figure out the simplest process for this.

Say I have one table, authors, with columns author_pk and author_name.
I have another table named books with columns book_pk, book_name, and author_fk.

Now I also have a form with two text fields: book_name and author_name.

I know how I can insert the author_name into authors and get the last insert id to use for the insert into the books table, but how do I prevent inserting the same author twice.
For fun, lets assume that whoever is doing the data entry will correctly spell the authors name every time.

The two ideas I've come up with:
A: Use a select menu instead of a text field for the authors name and have one option on the menu for a new author. On submitting the form or selecting the "new author" option, the user is prompted to enter the new author's name.

B: Before attempting any insert, check the authors table to see if the author already exists, and if it does, select the PK to use in the insert for the books table.

I tend to lean towards option B as it seems like it would cause the least amount of distraction for the user and help speed data entry, however, in a highly normalized database, it would result in a large number of database queries for each form submission and could get very complicated very fast.

Am I on the right track with either of these, or is there another option that I haven't considered?

[Brad]

Option B is definitely the safest solution while minimizing UI changes. What about that solution or your schema makes you think it adds a lot of queries? It's just one (or few) query per attempted insert, and if you have reasonable indexes on the tables, it shouldn't be too bad for performance. How frequent are insert operations in this system?

Alternatively, if you're really going for a bare minimum number of DB query executions, you could consider using "INSERT... SELECT..." statements like this:

INSERT INTO authors (author_name) SELECT "Herman Melville"  WHERE NOT EXISTS (SELECT 1 FROM authors WHERE author_name = "Herman Melville";INSERT INTO books (book_name, author_pk) SELECT "Moby Dick", authors.author_pk  FROM authors WHERE authors.author_name = "Herman Melville" AND NOT EXISTS  (SELECT 1 FROM books INNER JOIN authors ON (books.author_pk = authors.author_pk)  WHERE books.book_name = "Moby Dick");

This assumes:
- you can use sequences and set defaults on the PK columns to select the next value from the sequences
- you can have books with the same title by different authors

These queries do the following:
- The first checks to see if Herman Melville exists, and it inserts only if it doesn't exist.
- The second checks to see if Moby Dick by Herman Melville exists, and it inserts only if it doesn't exist, automatically looking up the author_pk.

In these cases, you're still effectively running the selects, but you save an extra trip down the execution stack (and across the network or however you reach your DB) by rolling the selects into the inserts.

(Also, I write this from a Postgresql background. I assume your DB platform of choice can do basically the same thing.)

[Brad]

Another (somewhat crude/ugly) alternative would be to blindly attempt the insert into the authors table, but have a unique index on the author_name column. That would cause some sort of exception upon inserting the duplicate, and you could catch and handle that as a special case.

[ThunderPoit]

Now that's the kind of clever I expect from Brad

I did a bit more googling and found this page:
http://mikefenwick.com/blog/insert-i...-row-in-mysql/

I may have been over-thinking option b. Now I just need to write out my insert method to deal with the 7 tables I'll be working with.

[Gargoyle]

Just use the authors name as the PK. Wrap the author insert in a try/catch and check for a duplicate key error, then write the book record.

You could even take advantage of MySQLs built in key checking with something like:-

INSERT INTO `usage` (`thing_id`, `times_used`, `first_time_used`)VALUES (4815162342, 1, NOW())ON DUPLICATE KEY UPDATE`times_used` = `times_used` + 1

If you attempt to check for the existence if the author first, and then selectively perform the insert, and your client (I assume its a web app) has many users, then you could get a race condition.