Fresh Start :) Best way to handle Note fields

[mike60smart]

Hi

I renamed the ID Fields
I recreated the Subform
I colour Coded all of the Linking Fields and these should be hidden from the user.
You do not need the TableID field in both tblCustomers & tblJunctionNotes

This is the standard layout.

Not understanding what you mean by Multi Parent?

Can you give a real life example of what you need?

 

[GBalcom]

Hi Mike,
Thank you for your help with this. When I say "multi-parent". I meant that I will want the notes table to store "notes" records for the company table, and the contact table, and others as needed.

 

[mike60smart]

Hi
Personally I would have a specific table for the ContactNotes and any other Entity required.

 

[NauticalGent]

I've been taught that it's best to keep memo fields as a detached table, with their own ID, and a foreign Key for whatever they need to connect to. This keeps the Main table lean and mean.

I have never heard of this practice although I can see where it could come in handy with bloat. Using a "side-end" DB like Galaxiom has posted about would definitely be a excellent way to incorporate this idea.

 

[June7]

If I understand Pat's suggestions, having TableID field in tblCompany and tblContacts does NOT allow multiple note records for the same parent record. The other method using TableName field involves a compound key. I avoid compound keys whenever possible.

 

[Pat Hartman]

My suggestion does allow multiple notes as you can see.

I added the entity concept. See if you understand it now. The downside is that you can end up with unused records in the entity table if you start a record but do not finish it so you might want to do some occasional cleanup. I also got rid of the #error in the ContactID. I didn't really know where you were going with this. I generally use a login form and I capture the UserID from that but without a login, I use the UserName Environ() variable.

 

[GBalcom]

Pat,
Thank you again for your efforts. I'm getting closer, but I'm still wrapping my head around it. Right off the bat, you taught me of a new VBA event I've had plenty of opportunities to use, but never knew it existed. The Form_BeforeInsert event. To expand on your idea, would it be possible to wrap the insert into the tblentity in a Transaction, so if the user pressed cancel, you'd have the opportunity to rollback?


Something like this?

Not sure if the logic is sound, but it seems like it could be worth a try, and may catch the user just clicking in, then escaping out. But, the code may run so fast it doesn't catch it anyways.

What would you recommend to clean this up periodically? Some sort of delete query?



OK, now on to the main subject, the entity table. (shown below for the convenience of future readers)

I just walked through the example, by adding in some breakpoints watching what happens as we add new records. I'm starting to see the idea. So, there is never a conflict because a new Entity ID is created for each new record out of any parent table. Is that correct? Would you consider holding the ParentRecordID in the Entity table as well, for when $%&* hits the fan or is that bad practice? I think otherwise you wouldn't be able to sort out the mapping again if it ever broke.

Also, in regards to Referential Integrity, I noticed you have that checked on the relationships, with cascading deletes check, but cascading updates not checked. I've questioned what the right move is in general for RI. Can you explain why you prefer the current setup, or is it only in this instance that you would select what you did?

 

[Pat Hartman]

Using a transaction is the right concept except that we don't have control of the source code that runs behind the form so we would not have any way of doing that. The only way to do it would be to use an unbound form which causes a whole lot of other issues and I wouldn't go there.

This is a chicken and egg problem since the relationship is technically 1-1 except that 1 side is many tables. The method I coded for you allows RI to be enforced on the relationship. You can make the relationship go the other way by storing the TableID from each of the tables where you want to make notes in the entity table. In this case, you would have less of a risk of a partial update since you would insert the entity row in the form's AfterUpdate event (you can't use the AfterInsert event) but you would have to keep an internal variable that identifies this as a new record since in the AfterUpdate event you can't identify whether the action was add/change/delete. The process becomes more complex AND you can't enforce RI and you can't get rid of the risk of one table being updated and one not.

All-in-all,
Both methods have the same type of problem. But having a row in tblCompany with no matching row in tblEntity means that you could never create a note so you would have to have some way to identify and correct this problem at a later date when someone tries to create a note. However, there is less danger when you insert the row into tblEntity first because tblEntity is never used for anything. It is simply a way to get from here to there. You could clean up any rows that end up here by accident but leaving them should not cause any problem.

 

[GBalcom]

Using a transaction is the right concept except that we don't have control of the source code that runs behind the form so we would not have any way of doing that. The only way to do it would be to use an unbound form which causes a whole lot of other issues and I wouldn't go there.

This is a chicken and egg problem since the relationship is technically 1-1 except that 1 side is many tables. The method I coded for you allows RI to be enforced on the relationship. You can make the relationship go the other way by storing the TableID from each of the tables where you want to make notes in the entity table. In this case, you would have less of a risk of a partial update since you would insert the entity row in the form's AfterUpdate event (you can't use the AfterInsert event) but you would have to keep an internal variable that identifies this as a new record since in the AfterUpdate event you can't identify whether the action was add/change/delete. The process becomes more complex AND you can't enforce RI and you can't get rid of the risk of one table being updated and one not.

All-in-all,
Both methods have the same type of problem. But having a row in tblCompany with no matching row in tblEntity means that you could never create a note so you would have to have some way to identify and correct this problem at a later date when someone tries to create a note. However, there is less danger when you insert the row into tblEntity first because tblEntity is never used for anything. It is simply a way to get from here to there. You could clean up any rows that end up here by accident but leaving them should not cause any problem.

Pat,
Thank you for your response. I've played around with this some more over the past few days, and I really like the concept you showed in the example. I also see now that adding the ParentRecordID in the Entity table is unnecessary and wasteful. I believe this will be my path forward. As a bonus, it seems I can create a common subform for notes, that can be displayed in many parent forms. I will use two hidden fields in each parent form (TableName, and EntityID) to keep the VBA simple.

One final question; Let's say I will have tblNotes, and tblAttachments, that both play similar relationship roles. Both tables have records that each could relate to several other "parent" tables. Would you create an entity table for Notes, and one for Attachments, or use the same entity table for both?

 

[Pat Hartman]

I would use the same Entity table. Its only use is connections so you may as well use it for all of them.

 

[Pat Hartman]

Would you consider holding the ParentRecordID in the Entity table as well, for when $%&* hits the fan or is that bad practice?

I know this is an old thread so please let it "die". Someone brought it to my attention and I noticed that I didn't answer this question and it is important.

The answer is NO. That would create a pathological connection. You can't add the entity because the related record hasn't yet been added and you can't add the related table record because the entity has not yet been added.

 

[MajP]

I know this is an old thread so please let it "die"

That was my fault for bringing it back to life, but I thought the approach was interesting. So how to tackle the common note table for lots of entities. I simply do it with a Foreign key and the table name, but I kind of liked this idea.

 

[Pat Hartman]

Thinks, I first used it in the late 90's using COBOL and DB2

You almost never want to see all notes for all entities at once so the schema doesn't present any real problem in queries. You just include the reference table you are interested in. Ie, customers or vendors or whatever. But if you did want to see all notes for some reason that I can't fathom, you would do it with a union query and pick a couple of common attributes from the related tables.