Database design qn for one-to-many relationship

[First posted to comp.databases.ms-sqlserver, no replies yet - perhaps these newsgroups are more appropriate to the topic]

Hi,

I am trying to decide between two different ways of coping with a one:many relationship between db tables, and was wondering if anyone could advise on the pros and cons of the two approaches (or maybe recommend a third!).

It is for an asp.net application, where the user has a chance to add one or more notes to a particular Person, Company, Case, Event, PersonEvent* etc, thus generating one:many relationships between People:Notes, Companies:Notes, Events:Notes, PeopleEvents*:Notes etc.

Thus I have to be able to link one "parent" object (the Person, Company or whatever) to that object's multiple "child" notes. Here are the two ways I have thought of to achieve this:

FIRST APPROACH     tblNotes
    Fields: NoteID, ParentID, ParentTypeID(lookup to tblParentTypes)

    Info: 1, 5, 1
    Info: 2, 5, 4
    Info: 3, 2, 3

    tblParentTypes
    Fields: ParentTypeID, ParentTableName(VarChar)

    Info: 1, "tblPeople"
    Info: 2, "tblCompanies"
    Info: 3, "tblEvents"
    Info: 4, "tblPeople_Events"



****************************************************

SECOND APPROACH (using linking tables instead)

    tblNotes
    Fields: NoteID

    Info: 1
    Info: 2
    Info: 3

    tblPeople_Notes
    Fields: PersonID, NoteID

    tblCompanies_Notes
    Fields: CompanyID, NoteID

    tblEvents_Notes
    Fields: EventID, NoteID

    tblPeople_Events_Notes
    Fields: PeopleEventID, NoteID

One thing in particular disturbs me in the first approach, and that is the design of tblParentTypes, where I feel uncomfortable with the ParentTableName field being a VarChar that contains the name of the other tables to be referenced. I feel that this kind of connection between tables should be taken care of by db relationships, and not by having the name of a table as a field in another table.

The first approach does however have the advantage of requiring considerably fewer tables, and I think it makes writing the OOP layers easier too.

Thanks for any help,

JON *An Aside - "PersonEvents"

A PersonEvent is an object representing the presence of one particular person at one particular event; in general, People:Events is a many:many relationship, and there is a linking table in the db tblPeople_Events that represents that object, as follows:

    tblPeople_Events
    Fields: PersonEventID(PK), PersonID, EventID

I am actually a bit unsure about the whole concept of a "PersonEvent" being a separate object. A person, a company, an event - these are all clearly defined real-world objects, but a "PersonEvent" is not, and so I'm wondering if I have made some error in my design by having to create such an "unnatural" object.

This worry is compounded by the fact that I have had to add the field "PersonEventID" to tblPeople_Events to make it easier to link a Note to a PersonEvent - before I wanted to add the Note functionality, tblPeople_Events looked like this, which I believe is the "normal" way to design a linking table:

    tblPeople_Events (old design)
    Fields: PersonID(CPK), EventID(CPK)

Appreciate any advice on this point too! Received on Wed Mar 05 2003 - 08:28:26 CST