3 Replies Latest reply on Jan 7, 2016 3:00 PM by BillisSaved

    One to one or one to many?


      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.


      Thanks, Connie

        • 1. Re: One to one or one to many?

          It seems like you need a separate table called QUESTION. Then a join table between CHAPTER and QUESTION. perhaps called EXAM, that would store an instance of every time a question was answer by the chapter.


          There would be one record in the question table for every question ("submit a letter to the editor" )


          In the EXAM table there is a field for an answer and that would be the field you would reference when rolling up totals to the chapter. Assuming the records in the EXAM table are timestamped, you could filter the responses, by year/month/date range.


          Hope this helps get you started. I'm sure there are other ways, but this should work for you.


          Screen Shot 2016-01-07 at 11.34.56 PM.png

          • 2. Re: One to one or one to many?

            Thanks for your thoughtful answer.  I'll have to ponder it some more.  Not sure I see the need for the exam table, since in the question/activity table, we'd have the response -- what else would be there otherwise?  Thanks!



            • 3. Re: One to one or one to many?

              Good afternoon cmahan,


              I hope your day is going well. If the questions you ask are the same for each chapter and the never change, then I think you could reasonably get away with two tables - Chapters and Questions. Although, I've always been told that tables are cheap and fields are expensive when it comes to performance, so having an Exam table, which contains the questions, may save you some trouble in the future if you ever vary/add questions...but that's just a thought.


              God bless,