Re: many-to-one relationship

My feeling is that there is a "proper" way to do this, and the key to understanding it lies in recognizing that there are different types of notes, and there is also an underlying generalized entity you call a note. Each note type is a separate entity that has an "is-a" relationship with the note entity. For each node type entity, there is a simple many to one relationship to a specific table. All of this can be represented using standard ER notation.

So given table Foo, you have

Table Note - primary key NotePK
Table Foo - primary key FooPK
Table FooNote - primary key NotePK is also a foreign key on Note, foreign key FooPK references Foo.

For table Bar, you will need a BarNote table, and more tables for every noteworthy table you want to join to.

The advantage of this approach over a more flexible scheme where table references are resolved at run time are:

1. This approach allows full referential integrity enforcement at the database level.
2. This approach avoids application code which would otherwise have to be written to manage the note/table relationships.

The drawback is that more tables will be created. Well worth the trade-off, IMHO. "Steve Croft" <stevec_at_ditdash.com> wrote in message news:3B1084AA.3060902_at_ditdash.com...
> I am having a rather sticky modeling problem. We have a number of
> different tables, rows of which may have zero, one or more notes
> attached. A note table has a type id indicating the table, and an id
> indicating the row in that particular table.
>
> I suspect there is a better way to do this - is there? If not, how do
> you diagram it?
> I'm sure it's an obvious answer but I'm not seeing it :(
>
> Thanks in advance,
> Steve
>
Received on Sun Jul 22 2001 - 01:27:34 CEST