2

Hopefully the question explains it well. I have a DB for a Library. Since they can be used many times, and contains more data than just a name, I have a table for Authors. Then there's a table for Books. I have no problem linking Authors to Books via a column called Author_id.

What I'm trying to do is have a column called Author_IDs that contains a list of id's, since a book can have multiple IDs. In the Author_IDs column I have:

<id>3478</id>
<id>6456</id>

Using the ExtractValue function in MySQL I can link the table with one or the other id using: WHERE Author.id = ExtractValue(Book.Author_IDs,"/id[2]") // to get the second ID.

My question is, I want to be able to automatically display all of the authors of a book, but don't know how to link to it more than once, without looping. How can I get the results to show me all of the authors?

(Or is there a better way to accomplish this?)

Tanoshimi
  • 463
  • 1
  • 4
  • 11

3 Answers3

1

Firstly, I have to vote against your storage method. Storing data as xml inside a mysql column should be avoided if possible. If you use a normal approach you will find this problem to be much easier.

Create a table:

book_authors

book_id     author_id
-------     ---------
      1             1
      1             2
      1             3
      2             2
      2             4

Then to get all of the authors associated with a certain book it's a simple query. 

Select
   b.book_id,
   b.book_name,
   GROUP_CONCAT(a.author_name) AS 'authors'
FROM
   book_authors ba LEFT JOIN
   books b ON ba.book_id = b.book_id LEFT JOIN
   authors a ON ba.author_id = a.author_id
GROUP BY
   ba.book_id
Ben English
  • 3,900
  • 2
  • 22
  • 32
  • Part of the reason I'm using XML is that there are going to be fields that contain multiple items that will not be done as seperately fields. Take the quotes by famous people and the reviews on the back of the cover. I want them done seperately, but in the same field, but do not want a whole nother table for it, since each is only getting used once. That being said, this seems like a decent way to do it, so there's a third table that contains the list of book_id's that go with the author_id's? – Tanoshimi Jan 18 '12 at 20:17
  • Fortunately, we're still in the design phase, and no data has been added yet. But I have the feeling that we will need multiple entries for almost every column. If that is the case, do I need to make seperate tables for every item, then seperate tables for connecting all of them? Aside from Author, take genre. A book can fall under more than one category in this case. Keyword I assume can just be done as a comma delineated list. That seems like a lot of tables by the time it's done. – Tanoshimi Jan 18 '12 at 20:39
  • To be clear, there's nothing in the Book entry that links it to the Authors. The only linking is done by the addition of the books_authors table? – Tanoshimi Jan 18 '12 at 20:50
  • I'll try and answer your questions in order: With regards to the XML. You SHOULD use separate tables for each of the items you mentioned. You can accomplish this with 1 table for all of your famous quotes across all of your books. There is no real penalty for adding tables so I'm not sure why you're so hesitant to avoid it. This is really a fundamental principal of database design. Please for the sake of all that is holy use separate tables instead of XML. Yes, that is a 3rd table that contains book_ids and author_ids, with 1 row for each book-author combination. – Ben English Jan 19 '12 at 15:32
  • Again, don't be afraid of more tables. What you are describing with the comma delimited list as well as the xml is not Normal Form. You will regret making these decisions later. You can create a generic metadata table if you want to combine some of these auxiliary tables. You would have columns like `book_id, data_type, value` so for the keywords example you might have `1, keyword, science` – Ben English Jan 19 '12 at 15:38
  • As for your last question, yes that is the only link between authors and books. – Ben English Jan 19 '12 at 15:39
0

Not sure I understand completely. Could something like this do the trick?

select a.* from tblBooks b left join tblAuthors a on (b.authors = concat('%', a.id, '%') where b = 'book id';

tim
  • 2,530
  • 3
  • 26
  • 45
  • not sure I understand what that does (still relatively new to MYSQL). I'm looking to get all of the authors listed in a single book entry. So if "ABC" was written by "John Smith" and "Lisa Jones", that's two seperate entries in the Author's table, but I need to see both of them when I query the "ABC" book. does that make sense? – Tanoshimi Jan 18 '12 at 20:10
  • Can we see your actual columns? I'd go with Bens suggestion unless its too much rewriting. – tim Jan 18 '12 at 20:22
  • Fortunately, we're still in the design phase, and no data has been added yet. But I have the feeling that we will need multiple entries for almost every column. If that is the case, do I need to make seperate tables for every item, then seperate tables for connecting all of them? Aside from Author, take genre. A book can fall under more than one category in this case. Keyword I assume can just be done as a comma delineated list. That seems like a lot of tables by the time it's done. – Tanoshimi Jan 18 '12 at 20:37
  • Go with three tables: books, authors, authors_to_books – tim Jan 18 '12 at 20:40
  • It's looking more and more like that's what I'm going to have to do. But what happens when it gets more complicated, like I have to add another table, say for Genre? – Tanoshimi Jan 18 '12 at 21:20
  • Just add tblGenres with the columns genre_name, book_id – tim Jan 18 '12 at 21:45
0

I would have done it like this

Structure

tblBooks
--------
book_id
book_name

tblAuthors
----------
author_id
author_name

tblBooksToAuthors
-----------------
id
book_id
author_id

Query

select a.*
from tblAuthors a
left join tblBooksToAuthors b2a on (b2a.author_id = a.author_id)
where b2a.book_id = {your book id};
tim
  • 2,530
  • 3
  • 26
  • 45