Tuesday, February 14, 2012

Design advise

I have been given a DB that currently stores Organizations. I need to add the ability to track multiple contacts for the Organizations. The DB has several one-to-many relationships on the Organizations table, such as Documents and Notes. I would like to track these by contact as well as the organization.

Is it possible to add a tblcontact and a one-to-many relationships to the Organizations table as well as to the tblDocuments and tblNotes?

I appreciate any suggestions.

tblOrg (OrgID)

tblDocuments (DocID, OrgID)

tblNotes (NoteID, OrgID)

tblContact ?

I also wanted to mention that we would be tracking organiztions and individuals.

I appreciate any suggestions on how to add to the existing Database.

|||

Hi Jack,

sure we can add a relation between the tblContact and tblOrg.

If your Documents and Notes belongs to a contact not Org, you need to create on-to-many relationship from tblContact to tblDocuments. When you need to track documents of an Org, you can first get the contacts and then get their docs.

|||I think I'm going to stick with the one org table and put everyone in that table. I will add a way to determine if the record is an organization or an individual (record type). I can create a relation table that contains the followng four fields (orgid, relationshipid, relatedorgid, reciprocalid).

No comments:

Post a Comment