How would I associate a "Note" class to 4+ classes without creating lookup table for each association.

Question

I'm creating a project tasklist application. I have project, section, task, issue classes, and would like to use one class to be able to add simple notes to any object instance of those classes.

The task, issue tables both use a standard identity field as a primary key. The section table has a two field primary key. The project table has a single int primary key defined by the user.

Is there a way to associate the note class with each of these without using a separate look-up table for each class?

I'm not so sure my original idea is a decent way to implement this. I considered the following (each variable mapping to a field n the notes table.

    Private _NoteId As Integer
    Private _ProjectId As Integer
    Private _SectionId As Integer
    Private _SectionId2 As Integer
    Private _TaskId As Integer
    Private _IssueId As Integer
    Private _Note As String
    Private _UserId As Guid

Then I would be able to write separate methods (getProjectNotes, getTaskNotes) to get notes attached to each class.

I started writing this a few weeks ago but got pulled away before I could finish. When revisiting this code today my first thought "this is retarded". Thoughts on drawbacks to this design?

Answer 1

why not have a structure as below

Notes

|Col Name  |  Type
|NoteId    |  Int 
|NoteType  |  Int (decides whether its a project, task, etc note)
|Note      |  Varchar

This would allow you to store an indefinite number of Note Types in one table. You could use typeOf(ClassName) to get its name to insert the class type in the NoteType column - meaning you dont have to deal with lookup tables etc and more importantly dont need to deal with a code change on the Notes class or an enum (for the note types) each time you want to add Note Functionality to another Class.

Answer 2

So what you need is a one-to-many between four distinct classes and the notes. Which in db terms means the notes get the foreign keys and that's the path on which you started with all the separate integer id's.

No matter what you come up with, you will have to deal with the fact that you have an "exception" in your identification mechanism that is the cause of your trouble: the section class that has two integers for its primary key.

But you can build on the similarities between three of your four classes by using a NoteType and a single identifier.

Private _NoteId As Integer
Private _NoteType As Char (or Integer if you like)
Private _ForeignId As Integer
Private _Note As String
Private _UserId As Guid

Dealing with the section class then becomes relatively simple: simply add a second identifier that is only required when the _NoteType indicates a Section.

Private _NoteId As Integer
Private _NoteType As Char (or Integer if you like)
Private _ForeignId As Integer
Private _SectionId2 As Integer
Private _Note As String
Private _UserId As Guid

_NoteType would dictate whether the note is associated with a Project, Section, Task, or Issue and would also dictate whether the _SecondId was needed or not. A class won't have difficulty enforcing the constraints on the _ForeignId and _SectionId2 based on the value of _NoteType. And a database could do this using declared constraints as well.