Relationship for a table containing all Comments from the database

[Luthius]

Hello folks
I would like your assistance on how to design a good relationship between tables where we will have a table only for Comments that we will use for the whole system.Instead a field for comments on each table, I will have a table that will contain all the comments so I just need to do a relation between tables.
Find attached the image with the relationships I was imagining.
I created one table intermediate so can "receive" the Primary Keys as per table "demands".
With this we can have more tables and the comments will be related on this intermediate table. What do you think??? I need to find a way.

 

[Micron]

I would forget about your goal. There is no valid reason for not having a table of comments pertaining to faults and one for issues unless maybe you're trying to win a contest to see who can make a db with the least amount of tables. You might win the prize, but the loser will be whoever has to use the db. The primary problem with your approach is that you're trying to link fault and issue id fields to the same field in a junction table. I obviously don't see the whole picture, but it's not logical to presume that you can guarantee one id will never exist in the other table - ESPECIALLY if they're autonumber fields.

 

[xenou]

The comment table can have its own primary key (autonumber) and all the other tables can reference the commentID as a foreign key.

 

[Luthius]

The comment table can have its own primary key (autonumber) and all the other tables can reference the commentID as a foreign key.

I think the problem will be the interface with the user, I mean, I cannot do correlation using a SubForm on the main form (Issue Form or Fault Report Form) because the error of

"The Microsoft Access database engine cannot find a record in the table 'tblComments' with key matching field(s) 'fkComments'

.
This way will be necessary create the comment first and them select the comment from a list. I mean, will be inverted the reason of comments.

 

[xenou]

"The Microsoft Access database engine cannot find a record in the table 'tblComments' with key matching field(s) 'fkComments'

I've never seen this error. What did you do to get this error?

BTW you probably don't need comments in all your tables and you may find it easier after all to just put a comment field in the tables that need them. My only advice would be to limit them to 255 characters and use a regular text data type rather than a memo field data type (only because I like simple data and comments can get very messy if you start getting people pasting their life stories into those fields).

 

[Luthius]

It is the Error 3101 of Microsoft Access.

This Comments field is very required and specially for database needs (Issues and Faults) when for some issue or some fault you need to report the latest update of the situation by making comments. This condition is very used on IT software for manage Issues by tickets and as well for condition of equipment on industry when we have history of issues and faults.

 

[xenou]

The error number is interesting but I was more interested in how you built the form to get this kind of error. I've never seen it before and you certainly can have foreign keys in tables without getting that error. So there must be something about the way you did this that is creating the possibility of this error occurring.

Are you really saying all of your tables in your entire database require a comment field? How many tables is that?

 

[Luthius]

Will be about 4 or 5 Tables.
tblFault
tblIssues
tblOrderDetails
tblEmployeesAppraisal

 

[xenou]

Okay. Well, I think its easiest to have the comment field in those 4 or 5 tables. But otherwise, I'd set it up as I described above, with a comment table using an autonumber primary key, and have the foreign key in the four or five tables.

You should be able to handle the cases where there is no match, which means there is no comment.

If you want to go the other way then you can try it. Have the foreign key in the comment table. But it will be difficult unless all the tables that use comments have primary keys that are the same number of fields (if they have composite primary keys) and the same data type(s). It will also constrain future use of the table because that will mean any tables in the future that use comments will also need to have the primary keys the same number of fields and same data types as in the comment table. All of that sounds very complicated to me, compared to just having a comment table with an autonumber primary key (or just putting the comment field in the tables that need comments).

 

[xenou]

Note that in general - the foreign key in one table is the primary key in the other (or vice versa).

So, given one of your tables, such as tblFault,
either:
tblComment primary key is a foreign key in tblFault (supports one comment)
or
tblFault primary key is a foreign key in tblComment (supports many comments)

probably all of this assume each table has its own comment table, not one that is used for all tables across the database - I'm with Micron being concerned about how this will work in the long run.