Ms-access – How to update a yes/no field to yes when all related fields in another table are marked yes

It sounds like the main issue here is that the attributes of certain entities (such as characters) can change over time, and you want to be able to track all the changes.

One way to do this might be to think of it as a data versioning problem.

One possible schema might look like this:

book
----
  ID
  name
  sequence_number (which book it is in the series)
  start_date (use this to track the dates that book covers)
  end_date (use this to track the dates that book covers)

character_base
--------------
  id (PK)
  first_book (FK to book.id)

character
--------
  id
  base_id (FK to character_base.id)
  version_number (in your case, this is probably optional)
  firstname
  lastname
  nickname
  hometown
  notes
  birthdate
  rank (fk to rank reference table: rank.ID)
  start_date (the begining of the time range for which this version is valid)
  end_date (the end of the time range for which this version is valid)

rank
----
  ID
  name

Now, whenever you create a new "version" of your character, you need to create a new record in character, but make sure you point the base_id to the same value as the previous value. Also remember to always populate start_date and end_date and ensure there are no gaps in the range.

To find out which version of a character is present in which books, you'd have to join character to book with a condition such that the character's version start and end date falls within a book's start and end date.

You can apply the versioning pattern for character to other entities that can be versioned, such as platoon or company if their attributes (such as name) need to be tracked over time.

If you want to keep things simple, it may be enough to add organization(organization_id PK, ...) table and person_organization_relationship (person_org_relationship_id (PK) , person_id,organization_id ,reltype_id , date_from, date_thru). Depends on requirements you may or may not add unique constraint, on , for instance, (person_id, organization_id,reltype_id). I would reuse the same relation table which is used for describing relationships between persons. In many cases such model will be working just fine.

If more general model is needed, concept of Party can be added. Party can be a person, or organization, or something else that interacts with the system . For instance if RDBMS supports enums, you can have

PARTY (party_id PK, party_type enum('person','organization'), [common attributes]);

Then you either add 2 tables, PERSON and ORGANIZATION (which both have party_id as a primary key and foreign key to PARTY) and store their attributes separately, or store all attributes in PARTY table.

Concept of Party lets you store any type of relationship between person and organization,or 2 organizations , or 2 persons in one table. Moving forward, it'll make other things easier to implement. For example, various contact information such as address(physical or electronic), phone, etc can stored in one place . On the other hands, it has its price. The more universal model you are implementing, the more time you'll need to spend in the beginning.

So I'd recommend to use simplest model that satisfies requirements .

Some notes regarding current physical implementation of relationship table:

     UNIQUE KEY outer_affinity (reltype_id,person_id1,person_id2), 
     UNIQUE KEY inner_affinity (reltype_id,person_id2,person_id1),
     KEY has_relationship_to (person1_id,reltype_id),
     KEY has_relationship_by (person2_id,reltype_id)

1. It's better not to have the least selective column (reltype_id) as a leading column in outer_affinity and inner_affinity
2. For enforcing uniqueness it's enough to have one of outer_affinity, inner_affinity
3. I'd start with just two indexes UNIQUE KEY UQ_relationship_person_1_person_2 (person_id1, person_id2, reltype_id) and INDEX IDX_relationship_person_2(person_id2) (depends on typical queries it might be useful to person_id1 as second column to this index).
4. The table seems to miss date_from (and maybe date_thru) attribute .