Notes table, one or two? (1 Viewer)

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 18:47
Joined
Sep 12, 2006
Messages
15,614
That makes sense. If you do not need to manage the separate comments (sort, filter, etc) you can do a running log in one field. I usually have a button to add the date to the comments to facilitate that.
Code:
Private Sub cmdDate_Click()
   Dim strDate As String
   Dim SomeTextBox As Control
   Set SomeTextBox = Me.txtSomeTextBox_Comments
   strDate = Format(Date, "yyyymmdd") & "-"
  If Trim(SomeTextBox & " ") = "" Then
    SomeTextBox = strDate
  Else
    SomeTextBox = SomeTextBox & vbCrLf & strDate
  End If
  SomeTextBox.SetFocus
  SomeTextBox.SelStart = Len(SomeTextBox) + 1
End Sub

Then my running comments look like
20180904 - Some long comment here
20180905 - Some more comment
....

Yes, that's what I meant. I generally add the date and user who added the added the comment, and the comment.
 

Cronk

Registered User.
Local time
Tomorrow, 05:47
Joined
Jul 4, 2013
Messages
2,770
Doc


need for THREE key fields


Yes but my point was about scalability. Attachment show relationship diagram I had in mind which only has one Notes table (plus another type table) providing for 2 tables shown on the left or 22 tables.
 

Attachments

  • Relationships.png
    Relationships.png
    16.8 KB · Views: 107

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 13:47
Joined
Feb 28, 2001
Messages
26,999
@Cronk:

Obviously there can be a situation where this is actually necessary, and if it can be shown through appropriate data requirements analysis, I would say (as I have frequently in the past) that "data rules all." Having said that, I have to also say that the conjoined notes table seems contrived and therefore unnatural. It APPEARS to violate the "purity of intent" concept.

I.e. you would have gotten to the point of having a separate NOTES table for table X because of size considerations and thus you got there by splitting the notes OUT of table X. But that didn't break the ancestry of the table. The NOTES_X table would exist only because X existed. Their key fields are still related. But the moment you conjoin the NOTES_X and NOTES_Y tables, you have altered that relationship. The key field that would have selected a particular record from the X table now isn't a "pure" foreign key any more. To me, this idea muddies the waters considerably (and thus reduces the clarity of the relationship.) I can only say that to me, it "FEELS" wrong.

As I said earlier, if you could show me a non-contrived situation where such a table setup was required, you might convince me. But absent a real-world example that requires such a setup, I remain unconvinced.
 

Users who are viewing this thread

Top Bottom