Notes table, one or two?

[Ricter]

Hi all,
I am starting a project. Two main tables to start, Vendors, and Items. One to many relationship. I want to have vendor notes, and item notes, also one to many.

Should I create a Notes table, and link both Vendors and Items to it, or two notes tables, like VendorNotes and ItemNotes?

There are about 100 vendors, not likely to grow much, and about 2500 items altogether, also not growing fast.

Rick

 

[Pat Hartman]

You need two tables. One to be the child of Vendor and the other to be the child of Items.

Keep in mind that the foreign key in a child table points to the parent table. You can't have one field pointing to two separate tables. There would be no way to enforce RI. So maybe you think - ah ha! I'll just have two FK fields and only one will be filled -- Don't do it. You aren't saving anything by making this unnecessary complication.

This is one case where it makes logical sense to have two tables with identical formats. Therefore, you could use a single subform to manage the notes. You would just populate the subform's RowSource from the parent form so that it selects data from the correct table. However, in practice, I would almost always use two separate forms just to keep things simple.

I will point out that there is actually a situation where you might implement something like this. In some applications where you have entities that are handled differently and require diffeent fields, you might define an Entity table. In a simple example, you would have two tables that are 1-1 with Entity. One would be Individuals and the other would be Companies. Both Individuals and Companies have Addresses and place orders. So, the Entity table includes the primary key and CustomerNumber if there is one as well as any other common fields and finally the type field which specifies whether this Entity is an Individual or a Company. All related tables such as orders, addresses, contact info relate to the Entity table rather than to Individuals or Companies.

 

[bob fitz]

You need two tables. One to be the child of Vendor and the other to be the child of Items.

Keep in mind that the foreign key in a child table points to the parent table. You can't have one field pointing to two separate tables. There would be no way to enforce RI. So maybe you think - ah ha! I'll just have two FK fields and only one will be filled -- Don't do it. You aren't saving anything by making this unnecessary complication.

This is one case where it makes logical sense to have two tables with identical formats. Therefore, you could use a single subform to manage the notes. You would just populate the subform's RowSource from the parent form so that it selects data from the correct table. However, in practice, I would almost always use two separate forms just to keep things simple.

I will point out that there is actually a situation where you might implement something like this. In some applications where you have entities that are handled differently and require diffeent fields, you might define an Entity table. In a simple example, you would have two tables that are 1-1 with Entity. One would be Individuals and the other would be Companies. Both Individuals and Companies have Addresses and place orders. So, the Entity table includes the primary key and CustomerNumber if there is one as well as any other common fields and finally the type field which specifies whether this Entity is an Individual or a Company. All related tables such as orders, addresses, contact info relate to the Entity table rather than to Individuals or Companies.

Hi Pat
Can I just ask why it would be necessary to create extra tables instead of just adding memo fields to existing tables.

 

[Ricter]

Hi Bob,
Can't speak for Pat, but the reason I'm using a child Note table is because a Vendor can have one or more notes, and an Item can have one or more notes. I did consider using a long text field for each, and add notes "blog style", but I want to be able to set a status to a note, like "active", "expired", etc.

 

[Ricter]

You need two tables. One to be the child of Vendor and the other to be the child of Items...

Thanks, Pat, awesome answer with extra points to think about, too.

 

[Pat Hartman]

Can I just ask why it would be necessary to create extra tables instead of just adding memo fields to existing tables.

@Bob,
The operative word was "notes", plural. If you have more than one of something, you have many and many requires a separate table. Mushing multiple notes into a single memo field simply makes for searching and reporting issues. Besides, most of the time, you do not want to allow updates to notes. If you have something more to say, you add a new note. Sometimes, I allow an hour or two and allow the original note author to change a note during a small window of time. This should give the user long enough to enter his clinical and other notes and review them before committing them.

 

[Gasman]

Pat,
I must admit I would have gone with one table with a field to identify which type of note.
I realise that then I could not identify just by the keys alone, but is there any other reason why you would use two tables, or more, if each entity needed notes of their own?

 

[The_Doc_Man]

I'll toss in an opinion on the "why two separate tables" question.

First and foremost, I've made it clear that I'm a pragmatist, not a purist. However, if the notes are about two different things then for purity of normalization they should be in two separate tables. Consider this by working forwards from what this COULD have been (and maybe once was?)

It would have been possible to have a single, updateable vendor 'notes' field in the vendors table, and a single, updateable item 'notes' field in the items table. Since vendors and items are clearly separate entities, they each need their own tables. Then, per good normalization principles, everything about items would be in the items table, and similarly for vendors. So the notes would follow along with their related entities.

Fast-forward to the day when you realize that you might need to change the notes by splitting them into a pair of tables in a parent/child one/many relationship. But the item notes still belong to items and the vendor notes still belong to vendors. Based on the original separation of entity attributes, these (now extended) attributes still belong in separate tables.

Pat stated this next point eloquently in post #2. I'm just offering different words in hopes that it strikes the right note. The FK that links the notes to their parent entities would violate a basic normalization concept if you merged the two notes tables into one. That FK would no longer uniquely identify the specific entity to which it belonged, since one could easily imagine that the PKs of the two tables COULD be autonumbered keys. Which means that an FK of 12 might be item 12 or it might be vendor 12. That relationship would really mess you up for trying to maintain relational integrity, since it would ALSO be possible to have item 200 but not have that many vendors. So RI would both fail and succeed at the same time on the same record. To establish the relationship, Access would require that the FK has a corresponding PK, and an answer of "it does and it doesn't" won't work at all.

The principles of database normalization aren't intended to combine things by merging them but are rather intended to separate things based on their differences of intent or usage. The mechanical point of that separation is so that using simple keys you can use queries to rejoin things in separate tables when you need them to be rejoined, but you can leave them not joined when your operation doesn't require that extra information. For example, a vendor-centric report probably has no need to see an item's notes, because items (if treated at all) will be treated in terms of aggregates thereof.

But let's take the ultimate worst-case requirement for a "pure" Access BE. What do you do when the file gets so big that it becomes impossible to manage via simple Compact & Repair? We've had a few cases of that situation on the forum. It happens when the file gets above about 1.5 GB.

One solution is to remember that Access CAN deal with more than one BE file at the same time. So you would split the BE into multiple BE files. But if you had merged those Notes tables, you just made it a pain in the toches to perform that split.

That is one old philosopher's answer to WHY you would use two Notes tables.

 

[bob fitz]

@Bob,
The operative word was "notes", plural. If you have more than one of something, you have many and many requires a separate table. Mushing multiple notes into a single memo field simply makes for searching and reporting issues. Besides, most of the time, you do not want to allow updates to notes. If you have something more to say, you add a new note. Sometimes, I allow an hour or two and allow the original note author to change a note during a small window of time. This should give the user long enough to enter his clinical and other notes and review them before committing them.

Thanks Pat

The fact that it was plural had completely passed my by. Your additional remarks on the use of notes were enlightening. Thank you.

 

[Pat Hartman]

Gasman,
I know it seems like there is no reason to not combine the data but you forget the importance of RI. If you don't define declarative RI (let the database engine do it), you must create your own RI using VBA (unless of curse you don't care about data integrity). In this particular situation, when you define the relationship between vendors and notes, you might decide to not specify Cascade Delete because you don't want to allow Vendors to be deleted if they've ever been used. However, the rules for Items might be different and Cascade Delete might be more appropriate. Worse case of course is that you discover that you have different rules AFTER you made the decision to combine the two sets of data.

 

[Mark_]

@ Ricter,

Just to add to Pats very eloquent response, start thinking through any additional information that needs to be store with a note. You will often find that one "type" of note needs some fields that the other does not.

For a Vendor, [entry date], [entered by], [action date], and [assigned to] may all be fields added to allow notes to be more useful. Often these will be in the form of

"Need shipping to verify delivery address for order number 123-456 PRIOR to order being processed"

Assigned to "Candi"

Due by "20180622"

Entered by "Big Jim"

For an item your note may need an [expiration date] but not [action date] or [assigned to].

Yes, I'm using horrible, horrible, horrible names that should never be found in a real table. I'm using them very specifically to illustrate kinds of things to ask about, not giving a real suggestion on fields to add..

 

[gemma-the-husky]

Personally, I think it depends what sort of notes you want to have. I might just add a notes memo field to each table, rather than a separate table. It depends how you want to use these notes going forward.

 

[MajP]

I might just add a notes memo field to each table, rather than a separate table.

As the OP already pointed out that is not what this question is about. This is a child table because it is one to many. A vendor and Item can have many notes. The question is about can you have different foreign keys related to different parent tables. This would be a nonnormal design due to partial dependency. You can make this work with one table (and I have actually done this), but you need to understand if the benefits outweigh the drawbacks. As pointed out there are drawbacks with enforcing referential integrity.

 

[gemma-the-husky]

Late addition.

It still depends what you want to do with the notes.

You could have a vendor_notes memo field in the vendor table, and an item_notes memo field in the items table.

I often do this, and store the name and date/time of each addition. I lock the notes text box, and just allow a new "note" to be added to the notes history. You can then easily see all the "notes" history by just popping a msgbox, or just using ShiftF2/CtrlF2 (I forget which key offhand). It depends whether you need to sort/filter the individual notes.

That's what I meant.

 

[MajP]

You could have a vendor_notes memo field in the vendor table, and an item_notes memo field in the items table

.
No you cannot. As been pointed out multiple times in this thread the OP wanted the ability to assign multiple notes per item and vendor. Thus a one to many. This would not even be a question if they wanted a single note.

 

[jamesandersonicb]

it depends on your requirement that what kind of notes you have.

 

[gemma-the-husky]

.
No you cannot. As been pointed out multiple times in this thread the OP wanted the ability to assign multiple notes per item and vendor. Thus a one to many. This would not even be a question if they wanted a single note.

What I meant was a memo field provides a simple way of storing multiple comments within a single field. If you don't want to edit the comments, it might be as easy to do that, then to build and manage child tables.

Of course if you want to sort/filter the comments then this doesn't work, but if you just need to store a small number of remarks its quite friendly.

 

[MajP]

What I meant was a memo field provides a simple way of storing multiple comments within a single field. If you don't want to edit the comments, it might be as easy to do that, then to build and manage child tables.
Of course if you want to sort/filter the comments then this doesn't work, but if you just need to store a small number of remarks its quite friendly

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.

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

 

[Cronk]

Interesting topic.
I'll add another slant, not that I'm advocating one way or the other. Like Doc, I consider myself pragmatic.

From an entity point of view, notes could be considered the one entity, in one table with a Type to distinguish to what they relate.

If there were notes for employees, for company buildings, for ..., would one have separate notes tables?

Same as for people who are customers, managers, sales staff. I would guess most would go for one table with a Type field.

 

[The_Doc_Man]

Cronk, you would have separate notes tables for each associated entity because of the RI issues. Having a single notes table to cover multiple contributor tables would give you the need for THREE key fields in order to maintain normalization.

a) The TYPE of note
b) The PK of the "master" table for that type code
c) The Note number if you allow multiple notes for the same entity

If you make the notes table a one/one with its parent type table, the TYPE field goes away. AND if you ever have to split the BE into sub-files, you just LOST the ability to maintain RI since RI is in the BE file holding the tables to be related. If the parent and notes are no longer in the same file, you just killed RI.

As I am a pragmatist, I recognize that for small systems, that might work anyway. But I am also just enough of a theorist to recognize that manipulation of the notes list will become a confusing swamp that would eventually need to be drained.