Problem with multiple level relations

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there.

I have a database in which are 4 tables (well, more really but only 4 that
concern this question).

Tables:
Project Info (data is user modifiable)
Individual contact info (read only)
Individual contact changed info (modifiable)
Organization info (read only)

They are related thusly:
Project Info --> Indiviual's Info
Project Info --> Indiviual's Change Info
Indiviual's Info --> Org Info
Indiviual's Change Info --> Org Info

So I have a form which contains fields from all of these tables, info
related to the project, the indiviuals, and the organizations. If a user
needs to change some info they need to do so in the "Indiviuals Change" area
of the form which is reviewed later and incorporated into the Indiviual's
record later.

The problem is that with the Change Info table linked to the Org Info table
(which is read only) it no longer adds records automatically which are
changed. See, before linking the Org table, when someone changes something
it would grab their ID from the Project table and add a related in the Change
Info table automatically, but now it doesn't because of it being linked to
the Org table.

So, anyone have any suggestions on how to make this work better (or at all?)
Thanks very much,

ggggeo
 
They are related thusly:
Project Info --> Indiviual's Info
Project Info --> Indiviual's Change Info
Indiviual's Info --> Org Info
Indiviual's Change Info --> Org Info
Click to expand...

This really does not help a great deal: an -> arrow does not contain
nearly as much information as relationship does. For example:

ProjectInfo.BelongsTo (required=true) references Individuals

means that every project has to belong to exactly one Individual; an
individual may be owned by zero or many projects.

ProjectInfo.IsChanging (required=false) references IndividualChange

means a project can change zero or one individual (how is this related to
ProjectInfo.BelongsTo?), and an IndividualChange may be changed by cause
by zero or many Projects

etc etc.

I strongly suspect there is a Design Problem somewhere around the
Individuals and IndividualChange tables, but of course I cannot tell from
here.

B Wishes


Tim F
 
Tim, Thanks for the information. Maybe I can be clearer on the relationship
between tables.

tblProjectInfo.ID =(one to one)= tblContactInfo.ID
tblProjectInfo.ID =(one to one)= tblChangeContactInfo.ID
tblContactInfo.OrgID =(one to many)= tblOrgInfo.OrgID
tblChangeContactInfo.OrgID =(one to many)= tblOrgInfo.OrgID

The project table will only contain one record per person (each project has
its own database) basically on how many times we've contacted them for this
project and what their status is on the project (e.g. Not interested, Will
participate, etc.).

Thanks,
George
 
Tim, Thanks for the information. Maybe I can be clearer on the
relationship between tables.

tblProjectInfo.ID =(one to one)= tblContactInfo.ID
tblProjectInfo.ID =(one to one)= tblChangeContactInfo.ID
tblContactInfo.OrgID =(one to many)= tblOrgInfo.OrgID
tblChangeContactInfo.OrgID =(one to many)= tblOrgInfo.OrgID

The project table will only contain one record per person (each
project has its own database) basically on how many times we've
contacted them for this project and what their status is on the
project (e.g. Not interested, Will participate, etc.).
Click to expand...

It's not a lot clearer. Relationships have NAMES which indicate what the
real-life relationship actually is. The only bit that helps here is the
Status relationship but I still don't really understand what it means.
You have also mentioned a new entity of Contacts.

Another point is that one-to-one relationships are really pretty rare.
Yes, there are genuine reasons for them but one is always initially
suspicious, while two are almost certainly a Design Problem. What is the
reason for having three tables with one-one relationships?

What is the difference between ChangeContactInfo.OrgID and
ContactsInfo.OrgID?

Don't forget that DB Design is always about semantics -- it's words and
meanings and subtle shades of meanings. Students.LivesAt is not the same
relationship as Students.HomeAddress! I feel strongly that you would be
able to see your solutions more clearly with a more helpful naming
strategy: vis

Projects

Contacts

Individuals

Organisations

Changes // it's always a warning sign when tables have verbs in their
// names: it means you are capturing processes rather than
// things

Hope that helps


Tim F
 
Back
Top