Database design qn for one-to-many relationship
Date: Wed, 5 Mar 2003 15:28:26 +0100
Message-ID: <b451ds$6do$1_at_news.online.de>
[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
FIRST APPROACH
tblNotes
tblParentTypes
Fields: NoteID, ParentID, ParentTypeID(lookup to tblParentTypes)
Info: 1, 5, 1
Info: 2, 5, 4
Info: 3, 2, 3
Fields: ParentTypeID, ParentTableName(VarChar)
Info: 1, "tblPeople"
Info: 2, "tblCompanies"
Info: 3, "tblEvents"
Info: 4, "tblPeople_Events"
****************************************************
SECOND APPROACH (using linking tables instead)
tblNotes
tblPeople_Notes
tblCompanies_Notes
tblEvents_Notes
Fields: NoteID
Info: 1
Info: 2
Info: 3
Fields: PersonID, NoteID
Fields: CompanyID, 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 - 15:28:26 CET