Results 1 to 10 of 10
  1. #1
    squirrly is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Aug 2011
    Posts
    33

    Design dilemma; multiple tables with same foreign keys?

    Can I relate one primary key from one table to duplicate foreign keys in multiple tables? (I know, don't cringe!)

    For example, say I have a lost and found database where users might lose an item AND find an item. For my own reasons, I want separate lost and found tables and relate them to the items table.



    tbl_users
    userID (pk)
    name
    email
    phone

    tbl_items
    itemID (pk)
    item
    userID (fk)

    tbl_lost_items
    lostID (pk)
    item
    itemID (fk)
    date
    details

    tbl_found_items
    foundID (pk)
    item
    itemID (fk)
    date
    details

    I know the best approach would be to combine the lost and found tables. But again, for my own reasons, I want them separate. In fact, I currently have five tables I want to relate to the items table. Any suggestions?

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    But again, for my own reasons,
    You will be making more work for yourself, but that is your choice. Just having a field within the table designating the type of transaction (lost or found) would simplify your life.

    In terms of the structure you presented, why do you have the UserID field in the item table? The item table should just hold info about the items. I believe that you have to associate the user and item together in your transaction table. Also, you should not have the item name in the transaction table, only the FK to the item. Additionally, the words name and date are reserved words in Access, so they should not be used as table or field names.


    Something like this:
    tbl_users
    userID (pk)
    username
    email
    phone

    tbl_items
    itemID (pk)
    item


    tbl_lost_items
    lostID (pk)
    itemID (fk)
    userID (fk)
    transdate
    details

  3. #3
    squirrly is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Aug 2011
    Posts
    33
    "Additionally, the words name and date are reserved words in Access, so they should not be used as table or field names."

    Thanks for the prompt reply. The example I presented is purely example, so I used 'name' and 'date' to simplify the view. The example is not nearly as complicated as my current database, which I am trying to redesign to bring it "up to code" with the way I should have done it long ago.

    "You will be making more work for yourself, but that is your choice. Just having a field within the table designating the type of transaction (lost or found) would simplify your life."

    My tables have quite a few different field names - they don't match each other completely because the items are unique. Combining them all (which I attempted), created quite a headache and question of data loss. So in a nutshell, that is why I want to keep them separate. If there's an easy way to combine them all into one table, I'd love to know how!

    "...why do you have the UserID field in the item table?"

    I'm still trying to grasp the whole relationship thing with Access. So I guess I related the UserID field in the Items table because I thought they had to relate somehow.
    But I now see from your return example how the foreign keys in tbl_lost_items relate them.

    In your return example, did you intend to drop tbl_found_items? Or were you combining lost and found into one table?

    I really appreciate the help - I'm self-taught with no one to consult.

  4. #4
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    In your return example, did you intend to drop tbl_found_items? Or were you combining lost and found into one table?
    I just showed the one table as an example. If you intend to keep both found and lost tables, they should have similar structures in that they would both need the UserID and ItemID fields.

    My tables have quite a few different field names - they don't match each other completely because the items are unique.
    Can you make the fields more generic in an effort to have all transactions in 1 tables? Can you provide the names and functions of these other different fields in the tables?

  5. #5
    squirrly is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Aug 2011
    Posts
    33
    So, in answer to my main question, a primary key CAN relate to duplicate foreign keys in multiple tables? I know.... completely unorthodox, but.... it can be done.

    So, yeah.... I could create all fields from all tables into one table and also try to condense it somehow. But that's kinda what I tried already, and after toggling, cutting and pasting and toggling, cutting and pasting, I was ill. :/ It also created a HUGE table! I have a lot of info out on my website that I'd like to keep pretty much as is, so dumping fields is unlikely.

    I'll play with your example a bit and see if I can apply it to my current project. I may just be in for a long and exhausting redesign...... with toggling, cutting and pasting. :/

  6. #6
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Best of luck as you do your "toggling, cutting and pasting".

  7. #7
    squirrly is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Aug 2011
    Posts
    33
    THANK YOU!!! I may be back....

  8. #8
    squirrly is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Aug 2011
    Posts
    33
    Wow, good news.... I was able to cut/copy and paste easily by freezing columns, dropping row size way down and tiling windows. YAY! I'll have to double-check all records, but at least everything is in one table now.

    A weird problem developed during this process - my consolidated table rearranged the columns in datasheet view from my desired order of columns in design view. Any way to fix this? I'll start a new thread if necessary.

  9. #9
    squirrly is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Aug 2011
    Posts
    33
    Geez, never mind.... I just had to resave my order in design view.

  10. #10
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Glad you got it all worked out!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. No sure how to use foreign key in the design
    By RickyLing in forum Database Design
    Replies: 0
    Last Post: 07-25-2011, 03:04 PM
  2. Using composite keys but stuck badly in the design
    By hmushtaq in forum Database Design
    Replies: 2
    Last Post: 01-25-2011, 12:25 AM
  3. Foreign keys in a consolidated table
    By threepwoodjr in forum Database Design
    Replies: 3
    Last Post: 01-14-2011, 11:25 PM
  4. Multiple Foreign Help
    By Dalagrath in forum Access
    Replies: 2
    Last Post: 11-01-2010, 10:25 AM
  5. Primary/foreign keys + relationships
    By tripptofer in forum Database Design
    Replies: 0
    Last Post: 08-12-2009, 07:44 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums