unique across tables

[NogDog]

I'm working on an app for keeping inventory type info for books. So I'm thinking I'll create a book table and an author table. Then I can have an author_to_book table, allowing for the possibility of co-authors with a many-to-one relationship.

Where it starts to get messy, though, is that I want to avoid duplicate book entries. But this gets ugly as titles cannot be copyrighted, so it's completely possible that author A has a book named "The Book", while authors B and C may have co-authored a different book with the same title. (Theoretically I could require additional information such as ISBN # or publisher/copyright date which I could use to create a unique index in the book table, but for usability reasons I don't want to.)

So all I can think of at the moment is to allow duplicate book titles in the book table, then when inserting a new book, I'd have to lock the tables, check to see if the author(s)/book relation already exists, if not add the new book and then use its ID for the new author_to_book entry(ies), or if it does exist just use that existing book ID.

This seems workable, but feels kind of clunky to me, so I was wondering if any of you DB experts might have a better idea.

[johanafm]

When you say for "usability reasons", I'm assuming you mean that you don't want to force users to enter the ISBN. Usability is nice, but in this case you may have to use ISBN.

If I've correctly interpreted the solution you are proposing, there is no way to differentiate between book sharing the same title, so that it's possible that

Book			Author
Something		Jane
Something		John
Something		Doe[code]
Would mean
there is one book with 3 co-authors
that there aere two books with two co-authors and one author respecitvely
or even 2 books with two co-authors respecitvely (Jane could have been in on both books)
etc.

Could be messy.

But perhaps this could be of some use [url=http://isbndb.com/data-intro.html]ISBN remote data access[/url].

The only other way I see would be to trust the user to always input all authors of a book. That way you would know that when the same title is supplied again, it is an entirely new book. More or less. It could be the exact same book again, but with another spelling of an author name.

[NogDog]

One of the underlying goals of the app is to make as easy as [reasonably] possible for users to get info into their book lists. That along with the fact that not every book will necessarily have an ISBN # (non-US publications, self-published e-books, etc.) makes it difficult to require anything that would uniquely identify a book, other than its title and author.

My current thinking was that I might have:

book
-----------
id  title
==  =============
1   The Book
2   The Book

author
---------
id  last_name  first_name
==  =========  ==========
1   John       Doe
2   Mary       Contrary
3   Anne       Elk

author_to_book
-----------------
author_id  book_id
=========  =======
1          1
2          2  [co-authored with 3]
3          2  [co-authored with 2]

So then if a user wants to add "My Book" to their book list, they would select between "My Book" [1] by John Doe or "My Book" [2] by Mary Contrary and Anne Elk, and the applicable book_id would be used in the user_to_book relationship table. So I think I can make that work, but the "uniqueness" of book/author combinations would seem to have to be enforced programmatically instead of via database indexes -- at least as I understand them (perhaps a good time for me to learn how to use stored procedures 🙂 ). But I'm wide open to alternatives.

[NogDog]

A possibly silly thought: could such "cross-table uniqueness" be enforced through some sort of view, perhaps?

[johanafm]

In that case I actually don't see the problem. Since you let/force the user to choose between several books sharing the same title you will get a specific book id.

Thus you can enforce the author-book uniqueness with a multicolumn primary key.

CREATE TABLE author_to_book (
author_id,
book_id,
PRIMARY KEY (author_id, book_id)

[NogDog]

Yeah, that's basically what I was thinking. The only time (I've thought of) where it gets kind of messy in my mind is when adding a new book to the database, when the same title already exists but for a different author. I think maybe I just need to use transactions, perhaps?

1. start transaction
2. lock book table
3. select query to see if book title/author combination already exists
4. if not exists, insert book, get insert ID, and use to add author-to-book entry
5. unlock and end transaction

Make sense? (And thanks for your feedback -- even if it just ends up reinforcing my approach, that's still a good thing and much appreciated.)

[johanafm]

My pleasure 🙂

I actually don't see the need for a transaction.

LAST_INSERT_ID() is maintained on a per conncetion basis, so you can always get the last (automatically generated) insert id in this fashion

3. select query to see if book title/author combination already exists
4. if not exists, insert book, SELECT LAST_INSERT_ID(), and use to add author-to-book entry

As for using a transaction, the results will remain the same in both cases (one being the book/author does not exist, and the other when they do).
No transaction

1. book/author exist? - yes
2. someone else deletes it
3. you do nothing since it existed...

a. book/author exist? - no
b. you insert and get last insert id

With transaction

1. start transaction
2. check if book/author exist? - yes
3. someone else wants to delete book. they have to wait
4. you don't insert anything. transaction ends.
5. that someone else gets their query through. book is still deleted.

a. start transaction
b. check if book/author eixsts? - no
c. insert and get last insert id. will always be same as without transaction.
d. end transaction

And the only time I can think of things going bad here is if you allow a user to pull up a book/author and add co-authors later on. Between those two requests, someone else might have deleted the book entirely.
But the simple use of foreign keys will prevent author_to_book from containing things not in book or not in author. So if a reference integrity fails, you can detect it this way and either just let that slide and assume that the book should indeed be deleted, or you might as well just insert it anew and generate a new id for it.