Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2012
    Posts
    1

    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.

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    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
    With
    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


    StePhan McKillen
    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.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •