Re: Database design qn for one-to-many relationship

You'll find my comments below...

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

I strongly dislike this approach since it is not a good practice to mix data and metadata.

: 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

This approach is much better I think, but I have another idea too. Consider the following (I'm using DDL, and I suggest you the same when posting to c.d.t):

CREATE TABLE Entity (
EntityID INTEGER NOT NULL PRIMARY KEY,
...);

CREATE TABLE Person (
PersonID INTEGER NOT NULL PRIMARY KEY,
...
CONSTRAINT FK_P_E FOREIGN KEY PersonID REFERENCES Entity(EntityID));

CREATE TABLE Company (
CompanyID INTEGER NOT NULL PRIMARY KEY,
...
CONSTRAINT FK_C_E FOREIGN KEY CompanyID REFERENCES Entity(EntityID));

CREATE TABLE Event (
EventID INTEGER NOT NULL PRIMARY KEY,
...
CONSTRAINT FK_E_E FOREIGN KEY EventID REFERENCES Entity(EntityID));

CREATE TABLE Note (
NoteID INTEGER NOT NULL PRIMARY KEY,
EntityID INTEGER NOT NULL,
...
CONSTRAINT FK_N_E FOREIGN KEY EntityID REFERENCES Entity(EntityID));

: 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.

I don't see how does it make "writing the OOP layers easier". And, btw, The approach I suggested has fewer tables too... And, finally, when deciding how to model your data, one of the important questions is "what am I gonna do with my data?". Having two models that are both capable of representing your world, (and that are both well normalized, etc, etc, ...) you'll use the one that will make your data maintenance easier...

Regards,
Damjan S. Vujnovic

University of Belgrade
School of Electrical Engineering
Department of Computer Engineering & Informatics Belgrade, Serbia

http://galeb.etf.bg.ac.yu/~damjan/ Received on Wed Mar 05 2003 - 17:30:35 CET