Results 1 to 6 of 6

Possible to Uniquely Reference ANY Record so that One Foreign Key can Link to Multiple Tables?

  1. #1
    MarkMeer is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    4

    Possible to Uniquely Reference ANY Record so that One Foreign Key can Link to Multiple Tables?

    I've got several tables: People, Orders, Deliveries, etc. All of which have a AutoNumber field as the primary key.

    What I'd like to do it make a generic "Comments" table, that I can apply on all the forms (so people can leave comments).

    Presently, the only way I can see to do this is to have a separate comments table for each data table:

    • People (pk:PersonID) -> PeopleComments (fk:PersonID)
    • Orders (pk:OrderID) -> OrderComments (fk:OrderID)
    • Deliveries (pk: DeliveryID) -> DeliveryComments (fk: DeliveryID)
    • ...etc.


    But given that these "Comments" tables have identical fields (name, date, comment), it'd be nice if I only had to have ONE such table, that could store all comments for all records regardless of whether they come from People, Orders, or Deliveries.



    But for this, I'd need some sort of global unique record identifier (a "URID") to use as a foreign key so I could tie in any generic record to the "Comments" table.

    • People (URID) -> Comments (fk:URID)
    • Orders (URID) -> Comments (fk:URID)
    • Deliveries (URID) -> Comments (fk:URID)
    • ...etc.


    Is it possible to do this sort of thing in Access?

    Any tips/advice/links much appreciated!
    MM

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,327
    You might consider adding a field CommentType within tblComments then perhaps a unique composite index
    to see if that works/suggests some option with your setup.
    Also, be aware that Name and Date are reserved words in Access.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,417
    I would do the separate tables. Have seen this before and makes complete sense to me. If you amalgamate, you will only complicate relating comment type AND comment id to the specific thing the comment relates to (order, delivery, etc.), especially if there are several comments related to the same order.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.

  4. #4
    MarkMeer is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    4

    Lightbulb

    Quote Originally Posted by Micron View Post
    I would do the separate tables. Have seen this before and makes complete sense to me. If you amalgamate, you will only complicate relating comment type AND comment id to the specific thing the comment relates to (order, delivery, etc.), especially if there are several comments related to the same order.
    Thank you for the replies and suggestions. Appreciated.

    However, I disagree that it would complicate things - on the contrary, I think it would simplify things by avoiding multiple identically constructed tables. Especially if such a "comments" feature was intended to generically apply to any entity (you might want to allow comments on images, posts, logs, ...whatever).
    If there really were global unique record identifiers, then the linking of a comment to the specific thing is just a many-to-one relationship.

    I wonder: is it possible to uniquely identify a table?
    If so, then perhaps the primary key for records could be constructed (via macro) as tableID + autonumber. This would ensure the primary key for any given record is unique across all tables.
    Then, all tables' primary keys could be connected to the lone "Comments" table's single foreign key.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,417
    I wonder: is it possible to uniquely identify a table?
    Well, you're certainly entitled to you opinion. To me, that you would need to do as you suggest either supports my view that you're complicating things, or perhaps you don't fully understand normalization or how it should apply to this situation. WRT to your calculated PK, most developers will tell you that a PK should never be meaningful data, which is what you're suggesting you'd make it. Whether or not that is because you prefer a 'composite' table of comments for everything isn't clear to me. I just don't see how following the rules (having a comments table for every entity) is better than what you are asking about. Nuff said...getting off my soap box now.
    Last edited by Micron; 11-15-2019 at 12:01 PM. Reason: spelin and gramur

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,327
    MarkMeer,
    I'm not sure where exactly you are going with this or what the goal is.
    However, I did work with a table (oracle) several years ago/before retiring that was similar to this generally.

    Code:
    tblGenComments
    Id        autonumber of this record
    Typ      type of record this comment refers to (ref to Txt_Type lookup table)
    RefId   the id of the referenced record 
    Lang    the language of the comment (Eng/Fre)
    Comment the actual text of the comment
    The comments could be for

    Company Strategic Alliance(s)
    Company Profile
    Project Summary
    among others.

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

Similar Threads

  1. How to Link multiple tables to one field
    By roderickchandra@gmail.com in forum Programming
    Replies: 3
    Last Post: 05-26-2017, 12:51 AM
  2. Replies: 6
    Last Post: 05-06-2015, 09:01 PM
  3. Replies: 12
    Last Post: 12-14-2012, 06:25 PM
  4. Design dilemma; multiple tables with same foreign keys?
    By squirrly in forum Database Design
    Replies: 9
    Last Post: 08-16-2011, 10:43 AM
  5. Multiple tables served by one link
    By htchandler in forum Import/Export Data
    Replies: 3
    Last Post: 01-13-2011, 01:49 AM

Tags for this Thread

Posting Permissions

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