Re: one big table or many smaller tables

From: Steve Kass <skass_at_drew.edu>
Date: Fri, 19 Jul 2002 01:42:48 -0400
Message-ID: <3D37A6D8.3204A137_at_drew.edu>


I see your point. I was thinking of

base table

..... NoteRef
----- --------

         333

notes table

333 1 This is one note
333 2 This is another note

But that's not entirely satisfying, since 333 is almost like a key back to one of a variety of tables. I guess this is something to keep thinking about...

Steve

"news0.alterdial.uu.net" wrote:

> "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 Fri Jul 19 2002 - 07:42:48 CEST

Original text of this message