How to link books of series together

Best is to refer : Stairway to SQL Server Indexes

You leave your house to run a few errands. When you return, you find a message from your daughter’s softball coach waiting for you. Three of the girls, Tracy, Rebecca, and Amy have lost their team caps. Could you please swing by the Athletic Products Store and buy caps for the girls. Their parents will reimburse you at the next game.

You know the girls and you know their parents. But you do not know their hat sizes. Somewhere in your town are three residences, each containing a piece of information that you need. No problem, you’ll just call the parents and get the hat sizes. You reach for your phone, and you reach for an index – the white pages of your telephone directory.

The first residence that you need to reach is that of Helen Meyer. Estimating that “Meyer” will be located near the middle of the population, you jump to the middle of the white pages; only to discover that you are at the page whose heading says “Kline-Koerber”. You make a smaller jump forward and reach the “Nagle-Nyeong” page. One even smaller jump backwards puts you at the “Maldonado-Nagle” page. Realizing that you are now at the correct page, you scan down the page till you reach the “Meyer, Helen” line and obtain the telephone number. Using the phone number, you reach the Meyer residence and obtain the information you need.

You repeat the process two more times, reach two other residences, and obtain two more hat sizes.

You have just used an index, and you have used it in much the same way that SQL Server uses an index.

For more reading, you can refer to

• SQL Server Index Basics
• Kimberly's posts on Indexing
• Expert Performance Indexing for SQL Server 2012.

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.