Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
01-18-2012, 08:04 PM #1
- Join Date
- Jan 2012
- Thanked 0 Times in 0 Posts
How do I link to a table from an XML list of id's?
I know it's generally poor DB design to have more than one piece of data in a single field, but I have the following setup:
Database called Library
Table called Authors
Table called Books
Books links to Author via it's primary key. Since a book can have more than one author, I have a column in Books called Authors, that contains XML list of all the authors id's.
Is there a way to do this? I want to be able to search for all books by a particular Author, even if they're 5th or 6th id in the list.
01-19-2012, 12:00 AM #2
Ummm...why would you store XML data in a DB field in the first place?
But ignoring that...
Yes, it is generally poor DB design to have any kind of delimited list in a single field.
And yes, it leads you to doing things such as using XPath that you should never do.
Isn't there any way you can fix this DB design? Surely it wouldn't be that hard to run a one-time process that would go through an normalize the database and create a many-to-many table of BookAuthors. You would do that one time and then you'd have the performance--and query capabilities--that you are looking for.
***********Code:CREATE TABLE BookAuthors ( bookid INT REFERENCES books(bookid), authorid INT REFERENCES authors(authorid), PRIMARY KEY (bookid, authorid) );
Having said all the above, you *COULD* do this with a really ugly query.
Using LIKE will give you pretty ugly performance, so I sure hope your database is relatively tiny.Code:SELECT b.bookname, a.authorname FROM books AS b, authors AS a WHERE b.Authors LIKE CONCAT( '%>', a.authorid, '<%' )
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.