Re: many-to-one relationship

this is quite easy.

create sequence noteID_seq; -- produces a unique note_id for every note in the database.
create sequence someID_seq -- produces a unique ID for every 'something' in the database

SomeTable1 (..., someID, ...) -- data table for some data with or without notes
SomeTable2 (..., someID, ...) -- another data table for some data with or without notes
...
SomeTableN (..., someID, ...) -- another data table for some data with or without notes

NoteTable (someID, noteID, ...) -- table containing all notes

Any insert statements to SomeTables shoud use someID_seq.nextval and noteID_seq.nextval for the respective ID values. Whenever a row is inserted into SomeTable which has a note, a correpsonding entry in NoteTable should be made.

It is important to remember the "parent-child" relationship. A common mistake is forgetting that in a relational model, the parent table does not require any knowledge of the child table. This structure is very similar to a "bill-of-materials" or invoice structure where there is a master table containing header information and then a line-item table containing each of the line items associated with a given master element.

"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:11 CEST