Fresh Start :) Best way to handle Note fields

[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.

 

[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?

 

[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?

 

[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.