I've been working on a database for a while now -- and finally did some online training which helped a lot (though I've been using FM since its beginning, I've never had any formal training). I originally brought some data in from a spreadsheet as a separate table but then decided it should be part of the main table and moved it over. Now I'm not so sure.
Here is the basic layout:
Main table is a set of chapters around the country. In it I have chapter name, state, chapter code (which is the key field) and some other information (# of members, etc).
The data I'm struggling with is a collection of answers to a series of questions about the chapter's activities. To keep this simple, let's say it's their answers to five questions and each response is marked with an X. So if they submitted a letter to the editor, I have an X in that field. If they attended a meeting, I have an X in that field. So each chapter has one set of responses to these five questions -- one to one, right? Right now we are tracking two years -- so I have their responses from 2013 and 2014. So now each chapter has two sets responses to the questions, one to many. But since they are separate years, it could still be one to one. One set of responses for 2013 and one for 2014.
Flipping it, the response in 2014 to "submit a letter to the editor" could apply to multiple chapters so now I'm thinking it should be a relational table.
In case it's useful, activities are counted, and roll up to a score for each year. Our main focus is to understand each chapter's score for 2013 and 2014 and the flat file accomplishes this.
I know this is very basic to figuring out the structure of the database. Invariably, the examples all include customers, invoices, products, etc. and I get the logic there. Would love thoughts on how I should handle my example.