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.

 

[Luthius]

I was thinking in a different approach.
Instead do the relationship I can create an INNER JOIN query and do insertions and updates on my form having this query as RECORD SOURCE. So I will just need to create a Foreign Key as per needs of my new table on my database.
What do you think guys about this idea?
Operationally is working- But on long term I cannot precise how the data will behave.

 

[xenou]

Sorry, I don't know what you mean by "having this query as Record Source"...

 

[Luthius]

I prepared an example.
https://1drv.ms/u/s!AsiHEjr5VeOBjkR6DhH94vsexw0O

 

[xenou]

Hi, as a rule I never click unknown links at work (and only sometimes at home). Can you describe what's there?

 

[Luthius]

This is an example I prepared based on our discussions. The same of the pictures I already posted here with my doubts.
I saved on my OneDrive folder and share the file by link.

 

[xenou]

Hi, sorry I haven't seen the pictures and prefer not to access links at work. You'll have to try to put this in words if possible.

 

[Luthius]

Will not be a problem. I'm sure it was well explained and exemplified before.
Continuing with it below the SQL Statement for the query I mentioned before.

SELECT tblIssues.idIssue, tblIssues.issueStatus, tblIssues.IssueDate, tblIssues.IssueDescription, tblComments.idComment, tblComments.commentDate, tblComments.Comment, tblComments.fkIssueFROM tblIssues INNER JOIN tblComments ON tblIssues.idIssue = tblComments.fkIssue;

 

[xenou]

So that will work fine, but the table should only be used for comments that belong to Issues in tblIssues, not other tables. So I would call that the comments table tblIssueComments to be a little more clear.

 

[xenou]

Actually I guess for multiple tables you'd have to set it up like this:


Sample issue table:

-------------------------
| idIssue | Description |
-------------------------
|     100 | Issue1      |
|     101 | Issue2      |
-------------------------

Sample faults table:

-------------------------
| idFault | Description |
-------------------------
|     100 | Fault1      |
|     101 | Fault2      |
-------------------------


Sample comment table:

--------------------------------------------
| idComment | Comment  | fkIssue | fkFault |
--------------------------------------------
|       200 | Comment1 |     100 |         |
|       201 | Comment2 |         | 100     |
|       202 | Comment3 |         | 101     |
--------------------------------------------


Sample comment table (another option):

----------------------------------------
| idComment | Comment  | fkID | fkType |
----------------------------------------
|       200 | Comment1 |  100 | Issue  |
|       201 | Comment2 |  100 | Fault  |
|       202 | Comment3 |  101 | Fault  |
----------------------------------------



The first option has the disadvantage of repeating columns and some wasted space.
The second option has the disadvantage of only allowing one data type for the fk column.

 

[Luthius]

I cannot see any problem on "Wasted space" because usually on database we will always have some fields not filled, the same happening here.
Well, if there is another restriction on do this way - Would be good a good thinking on "long term". So far, I guess I can continue with this idea. What about?