Heres the scenario
I have one table for Borrowers with personal details
I have three tables (one for each category of item) called Books, Videos and CDs. These each have different types of fields. I want to be able to add further categories later (adding more tables)
One Borrower can have multiple Items.
One Item can have multiple Borrowers (weird I know but needed).
I would also like to specify a category and display all the Borrowers that have a Item of that category.
I want to be able to search an Item and see which Borrowers are associated with it and vice versa.
How do I go about making a Junction between a Borrower and a Record from one of the item tables based on category?
I figured a table of 'categories' would be needed but im not sure how to link it all up.
Thanks