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.