Re: one big table or many smaller tables

"Steve Kass" <skass_at_drew.edu> wrote in message news:3D348128.CE7800C1_at_drew.edu...
> 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.
>
I'm not sure I understand what you're saying - what would NoteRef look like?   If it points to a single note then it wouldn't work because each row in the base
 table can have multiple notes, if it indicates which table the note is for then it
would be the same for each row in the base table. Maybe I'm not getting it.

I agree with you about mixing the meta data and data, I could get around that
 with a 'note_table_code' and put that into the primary key, but that's getting
 more complex and I'm trying to simplify things.

> 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?
>
Yes, we have at least one table that has a separate note structure (that table has
several types of notes with differing attributes), that one has been pulled out.
Again this tends to make things more complex in that you have to remember  whether the table you're working with has it's own notes or uses the generic
 notes table.

> 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!
>
Thanks for your response, I think I'm going to end up going back to multiple  notes tables because I realized that we can pull disparate notes tables together with UNION, and at every turn with this (one big notes table)  model things instantly get complex - telling me it's going to generate more work than it saves.

David C.

> 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.
> >
snip. Received on Thu Jul 18 2002 - 18:56:41 CEST