01-23-12, 07:08 #1Registered User
- Join Date
- Jan 2012
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.
01-23-12, 08:12 #2(Making Your Life Easy)
Provided Answers: 10
- Join Date
- Feb 2004
- New Zealand
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 okhope this help
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
VB-NET based on my own environment started 2007
SQL-2005 based on my own environment started 2008
YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
THEY'RE BEHIND YOU FOR A REASON
01-23-12, 08:24 #3SQL Consultant
- Join Date
- Apr 2002
- Toronto, Canada
having separate tables for books, videos, and cds makes good sense, since they have different attributes (columns)
what's missing is a supertype table, and that's what you would link to the borrower table
alfihar, do a search for supertype/subtype