Which items are table entities and which are fields? I'm a mystery novelist with a series of three books that will end up at around 20 novels. I am having trouble getting the tables and fields sorted out.

I want to be able to:


create a list of all the people in the series
create a list of all the books in the series
search and find all the characters who have appeared in more than one book
which book a character first appeared
list a complete company or platoon by year
identify all characters with a serial number
Search for a  character in different companies, platoon, sections within the Battalion
Find the book where a character first appears


During the course of the series, people can be promoted or demoted (change their rank). They can be cross-posted within the Battalion from one Company to another (from a rifleman in Alpha Company to a Mortar man in Mortar platoon, Support Company). I have no idea how to represent this – or even if it can be done with one database.

Based on what I understand of normalization, I have reduced the data chunks into the following entities or fields:

Books by title
Years – 1964 -70
Months of the year (more than one book can appear in a year, no book appears in two years)
Battalion organization – the Companys
The platoons, sections
Character – last name, first name, nickname, where he came from in Canada, notes
Rank – (Which can be different depending on the year/book)


More information is available if required, please ask for any information regarding the Canadian Army of the time.
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.