Relationship for a table containing all Comments from the database

Luthius

Active Member
Joined
Apr 5, 2011
Messages
324
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.

c32cd5498d.png
 
Last edited:
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.

f06f98d65f.png
 
Upvote 0

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sorry, I don't know what you mean by "having this query as Record Source"... :(
 
Upvote 0
Hi, as a rule I never click unknown links at work (and only sometimes at home). Can you describe what's there?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.

Code:
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;
 
Upvote 0
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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?
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top