How can I do this query - design and search

[daviddth]

OK an unusual question. I need some ideas on how to design and query a particular group of things. The best way is to explain what I am trying to search. I run a website and part of that site is currently a list of common ties (character names) between the authors books. At the moment I have static pages showing:

 

Book 1 name

    Book 2/Short story name - List of common characters (Between Book1 & 2)

    Book 3/Short story name - List of common characters (Between Book1 & 3)

 

Book 2 Name

    Book 1 - List of common characters (Between Book1 & 2)

 

etc etc

 

Basically I now want to automate the retrieval of those lists, as well as make it easier to update them in the future. At the moment there are a lot of items on each of 5 pages, but that could change as it could take a lot of queries depending on how I do it.

 

Initially I was thinking of 4 fields - ID, Book1, Book2, List of common ties

but that still takes work to update, cross link etc as the same person might be in between 2 and 10 different books. That setup is easy to do using my current abilities, but I want to do it better and smarter.

 

Ideally I'd love to have 3 fields - ID, Persons name, "a list of the books that they appear in"

and then run a query looking for the current book in that list, and build a page for the individual book and all it's links. Problem is I really dont know how to get started on this, how to store a different number of items in that 3rd field (All items there are 9 character or less alphanumeric and no spaces, making that, at least, a little easier), and how to search and build up the output in some sort of order

 

I'd really appreciate some help with this.

[xylex]

You're trying to setup a many to many relationship schema. 

 

Try

 

Table of books

Table of characters

Table of book id's and character id's

 

And query for your sublists.