I have a database that tracks users, tickets, and inventory. I'm trying to consolidate tables. For example, currently I have multiple comment tables, tblUserComments, tblTicketComments, etc. I want to have one table for comments. However, I'm having a problem understanding how to deal with foreign keys. This is what I have:
tblUser
--tblUser_ID (pk, autonumber)
--tblUser_LastName
--tblUser_FirstName
--tblUser_Office
--tblUser_Phone
tblTicket
--tblTicket_ID (pk, autonumber)
--tblTicket_Title
--tblTicket_Description
--tblTicket_Status
--tblTicket_Opened
--tblTicket_Closed
tblComment
--tblComment_ID (pk, autonumber)
--tblComment_ObjectID (fk, ticketID, userID, etc)
--tblComment_Comment
--tblComment_DateTime
The problem I'm having is when I want to display all the comments for a particular user or ticket, how will it distinguish between users and tickets. In the foreign key field in the comments table, it will have 1. This could be user 1 or a ticket 1.
I thought about adding an additional field like tblComment_SourceTable but that's a lot of redundant data. I thought about adding a T or a U to the primary key but I don't know how to do that and I'm reading thats bad to add descriptive info to a primary key.
I appreciate your help. My knowledge with Access is basic and self-taught. I usually end up rigging it to work somehow and it's not the best way to do it.
-Mike