Re: one big table or many smaller tables

David,

  Would it be easier to have a big notes table that looks like

create table Notes (
  NoteRef int not null,
  NoteNum int not null,
  Note character varying (1000),
  primary key (NoteRef,NoteNum)
)

Then add a NoteRef column to each table that has associated notes, and make that refer to Notes.NoteRef. I have a feeling it's better to put a NoteRef value into the base tables. What you are suggesting is for the Notes table to contain a reference to a row in any of many tables, and if you need to use metadata (the table name) to identify something, you know you've done something that violates good relational principles.

The one thing that doesn't seem quite right here is that to indicate no note, you would most likely put NULL in a NoteRef column, but that should mean the noteref is unknown, not that it's known one doesn't exist. Solving that gets you back where you started, with a separate notes table (this time with noteRef id numbers, though, instead of long notes) for each base table.

So the next question is whether there are any other attributes of a note other than its contents - a date? an author? If not, is there a reason you need separate notes for one source item to be in separate rows?

Anyway, it's not perfect, but I think it's an improvement over a solution that mixes metadata and data. It's also a good question to think about!

Steve Kass
Drew University

"news0.alterdial.uu.net" wrote:

> Database theorists:
> I have a fairly complex data structure with about 110 tables, 11 of
> the tables have 'notes' related to them and each row can have multiple
> notes associated with them.
>
> We're considering two ways to model this, the simplest
> structure is to have a notes table associated with each table that will have
> notes, so you have
> table1_notes, table2_notes etc, each with the primary key of the
> related table in each row in notes.
>
> In this simple model to query the notes table it would be something like:
> "SELECT note_text FROM table1_notes with id=currentID"
>
> The other way is to have one large notes table (it would have about..
> I think it was 60,000 records in it, not enough that performance would
> be an issue), and have the primary key of each related table + the
> table name in the notes table, so you would specify the table name
> when you query the notes table, like:
> "SELECT note_text FROM notes with table_name='table1'
> AND table_id=currentID"
>
> I'm advocating the second method because in the interface design there
> will be several places where we need to pull together notes from
> multiple related tables, for instance if table1 is students and table2
> is classes and table3 is instructors and I'm at a student record and
> want to see the notes for the student and the classes and the
> instructor it seems to me it would be reasonable to modify the query
> pulling in the student notes:
> "SELECT note_text FROM notes, classes_students
> WITH notes.table_id = current_student_id
> AND notes.table_name = 'students' "
>
> and add notes for the classes, let's see:
> AND classes_students.student_id=current_student_id
> AND with notes.table_id=classes_students.classes_id
> AND with notes.table_name='classes'
>
> I would have to pick an n:n for the example, but there will be some. Anyway,
> assuming
> this pulls in the notes for the classes that the student is taking,
> modifying the query like this should pull in the notes for the student
> and the classes that that student is taking, and you could pull in the
> instructor notes as well, displaying all the notes for the three
> entities in a single grid. I believe so anyway - we're using SQL
> Server 2K, vb.net., Win2k Server with a dual processor compaq Proliant
> server.
>
> So my question is: do any of you see any problems with the one big
> notes table? If anyone has used a model like this (I've seen things
> modeled this way before but never used it on something this large and
> complex)
> I would also be interested in knowing that it works, any pitfalls.
> Thanks in advance for any answers, and for reading all this even if you
> don't
> have an answer (or opinion).
>
> David C.
> ------------------
> Database Developer
> ABC-CLIO
Received on Tue Jul 16 2002 - 22:25:12 CEST