Does this mean many-to-many?

[Isidore]

My database includes the tables, ORGANIZATION (name and address of a
business) and CONTACT (name and _personal_ address etc. of contacts at
an organization).

Key field for ORGANIZATION is OrganizationID. Key field for CONTACT
is ContactID. I currently relate CONTACT to ORGANIZATION by including
ContactID field in ORGANIZATION, but I don't think I can achieve what
I want to this way.

I keep these items in separate tables because people tend to move
around a lot in my business. They also frequently work for more than
one business at the same time, or leave and return frequently.

The Oraganization form I plan for this database will draw a Contact's
name from the CONTACTS. It will draw any on-premises contact
information (as opposed to personal contact info) from fields in the
ORGANIZATION table. (A contact's personal information, which tends to
remain stable, will be on a separate form).

I would like to set my database up in such a manner that
(a) if a person leaves an organization, I retain a record indicating
that my dealings with that organization were with that individual, and
(b) a record is maintained of all the organizations an individual is
or has been a contact for.

Can anyone suggest how I might achieve this?

Thanks in advance,
Isidore

 

[Pavel Romashkin]

Yes, it is a many-to many type of relationship. Record the assignment of
your contacts in another table, called, say, ContactAssignment. The
table needs to have the following columns:

AssignmentID (Autonumber, PK)
OrganizationID
ContactID
DateCreated
DateLeft

The point in using a separate AssignmentID as opposed to having a
compound Organization/Contact key is to 1) allow to preserve the
information if a Contact leaves the Organization and 2) allow the same
contact to again represent the same organization they left before.
DateCreated is the date the contact starts to work with organization,
and DateLeft is the date they leave this organization.

Pavel