Which items are table entities and which are fields

database-design

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.

Best Answer

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.

Related Question