Unanswered: Using a table of 'Categories' to specify which table to reference
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.
My question is why have a table for each category that going to make it harder for find anything
But in saying that
As long as each table has the borrowerid in it that will
Using you model I would create a catEgory table
Categoryid = pk
Borrowerid = pk form the borrower table
Category_table = the table name
Adate =autodate when it was added
.... Could add the category_table Id have but think it could be a overkill
But the you have to write some code so that when one of the other tables are updated you have to update the cat table also
This is me thinking out loud ok
hope this help
See clear as mud
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Progaming environment: Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010 VB based on my own environment: vb6 sp5 ASP based on my own environment: 5.6 VB-NET based on my own environment started 2007 SQL-2005 based on my own environment started 2008 MYLE YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.