Notes system in database

Question

I'm currently working on building an ERP/CRM database in Access 2013 to cover all non-financial aspects of running a business such as customer/supplier info, contacts, order and deliveries tracking, and that sort of thing.

We manufacture made-to-measure joinery products (windows, doors etc). One thing that I'm finding is that I'll need to have the ability to write notes about records in almost every table in the database. For example, I may want to write notes on the following tables:

tblAccount which holds customer and supplier info, e.g. "This customer always has special glass".
tblDelivery - "Customer has requested that driver parks at back of house on London Road".
tblQuote - "Quote does not include timber cladding for steels - supplied by others".

etc. etc.

So currently I have a tblNote which has the following fields:

NoteID - PK
AccountID - FK to tblAccount
DeliveryID - FK to tblDelivery
QuoteID - FK to tblQuote
Several more FKs to other tables which contain things I might need to keep notes on.
EnteredByUsedID - FK to tblUser of the database user who entered the note onto the system.
EntryDate - Date note was entered
NoteText - the text of the note
CommTaskID - Explained later - see below

When entering a new note, most of the FKs to other tables will be blank (except those which the note actually concerns). The notes will be viewed either on a subform, e.g. when browsing through customer accounts there will be a box which shows notes about that customer.

The reason for having notes in their own table is that each record in the database (e.g. a customer account) may need to have more than one note attached to it.

I also want to implement task assignment and tracking, and communication logging. For example when we receive a phone call from a prospective new customer asking for a quotation we would record that the call was received and when, the customer's contact details, note that they have requested a quote along with minor details about what they want quoted, plus flag it for action required either by a specific database user or with nobody specified. It might also be created as a

With this in mind I have tblCommTaskLog which has the following fields:

CommTaskID - PK
CommMethodID - FK to tblCommMethod which holds things like Phone call, Fax, Email and In Person.
AccountID - account record of the person (customer/supplier) making the contact
EnteredByUserID FK of user who entered this record.
EntryDate
IsActionRequired - Boolean
ActionRequiredByUser - FK to user table to flag who needs to take action on this item (could be blank if this action can be completed by anybody).
ActionCompletedDate - Date when the action was marked as completed.
ActionCompletedByUser - Another FK to user table for user who completed task.

As seen above there is then a CommTaskID in the notes table which would allow you to create note records which contain any relevant notes about the communication or task.

Basically my question is: Is there a better way to go about this? Am I missing something? It seems a clunky way to go about things but I'm unable to come up with a better one which follows DB normalisation rules.

Instead of storing foreign keys in the notes table, why not do the other way around and store the primary key from notes, NoteID, as a nullable foreign key in all the tables that can have notes, like tblAccount? — jpw
I addressed this in the question: "The reason for having notes in their own table is that each record in the database (e.g. a customer account) may need to have more than one note attached to it". I need a one to many so that I can have many notes for each record if necessary. — WhatEvil

Johnny Bones Johnny Bones · Accepted Answer · 2014-09-23T14:57:09

Your concept sounds right to me. I've had to integrate notes into an existing DB and you definitely only want one Notes table with a field that designates which table the note belongs to, and which Primary Key within that table the note is tied to.

So, I think maybe you want this:

NoteID - PK
TableID - FK to a new table, which stores the names of your tables
TablePK - FK to the Primary Key in the table referenced in TableID
Several more FKs to other tables which contain things I might need to keep notes on.
EnteredByUsedID - FK to tblUser of the database user who entered the note onto the system.
EntryDate - Date note was entered
NoteText - the text of the note
CommTaskID - Explained later - see below

This way, you'll know which table it belongs to, and which record in that table that it belongs to.

In a query, you can simply join TablePK to the key in your table, and filter TableID based on what table you're joining to.

Or, if you need to set up a relationship you can simply add a new field to all your tables with their TableID in it.

Notes system in database

2 Answers