Keeping track of contributors
Hi, I have just upgraded from FMP6 to FMP12. This may be a good opportunity for me to improve a flaw in the way I set up my database (about 14 years ago!).
This database keeps track of who has contributed articles to a monthly magazine. It consists of three files: "books," "rolodex" and "articles." Books is simply a list of the books that have been reviewed; it contains the book's title, author and publisher, and a record number. Rolodex is a list of all the writers who have written those reviews -- it contains the contributor's name, postal address, email address and "review history," and a record number. Articles joins the two files together and adds information pertaining the particular issue in production, so I plunk in the book record number and the contributor record number and the Articles fields get populated, plus I add stuff (word count, article type, article title, and so on).
This system works very well except for the review history. This is a plain text field that contains the issue info. One contributor might write for us more than once, so the field contains a string: "12/06, 11/05, 11/10" (i.e., someone who has written for the June 2012, May 2011 and October 2011 issues). This makes it difficult to find all the contributors who wrote in 2010 or all our contributors from the last five years, or whatever.
I can't figure out what's the best way to improve this. Does anyone out there have any suggestions, or can point me in the direction where I might see something similar that I could adapt?