database structure & base or calc?

[tryingtoleavethisdumsite]

I am in the planning stage of creating a system to keep my class records in order. It would be great if someone can offer any feedback or advice.

I am a teacher wanting to record the content of each day’s lesson (using a number of fields such as materials, activities, songs etc.), student attendance, student responses to some set questions (building a small profile of them such as likes and dislikes etc.), and similar content. I want to be able to use forms (with a limited set of fields for data entry), and reports (for example creating a chronological list of materials and activities used, or all the responses of a particular student).

Am I correct in the following numbered assumptions?:

1 The basic structure is a single database page similar to a spreadsheet, with a column for each of these fields:
Date
Student’s name (attendance: Y/N),
Question 1 student 1 (text field)
Question 1 student 2 (text field)
etc.
Question 2 student 1 (text field)
Question 2 student 2 (text field)
etc.
Materials (insert multiple items from a picklist)
Songs (insert multiple items from a picklist)
Activities (insert multiple items from a picklist)
New terminology (text field)

2 Each of the picklists can draw from separate databases entitled Materials, Songs, and Activities (and updating these spreadsheets with new materials will update the picklists in the main spreadsheet).

3 Because I want to use forms and reports, and draw picklist content from a separate database, I should use base and not calc.

Thank you very much, I hope someone can give me some pointers.

[LogicDaemon]

First suggestion: don’t use LO for this. Best way, I guess, is to use Google Forms or some online forms alternative.
(google forms’ responces are written to spreadsheet, which can be downloaded as an ods or edited online).

Answers to assumptions:

1. Best way to keep records in computer is using rows (new record → new row), all on one sheet / db table. Not separate pages as in a diary. And using multiple values “for cell”, though seem natural for humann reading, are problematic for automated analysis. Better, if you’ll end up with a database, keep record like this:

   • table “Students”: Student’s ID | Student’s Name

     key is Student’s ID

   • table “Attendance”: Date | Student’s ID | Present?

     key is date + Student’s ID

   • table “LearnTypes”: Type ID | Type

     key is Type ID. Contents of this table:

     1 : Question

     2 : Materials

     3 : Songs

     4 : Activities

     5 : New terminology

   • table “Learning”: Date | Student’s ID | Type ID | value

     key is another column, let it be just a serial

     In last table, keep single “value” for each record. But, for each student id, each learning type, and each date, there can be multiple records with different values (in other words, all will be the same but value and key).

2. This is possible with both sheet and db. Though with sheet it’s done with “Data → Validity”, you can insert sheet from another spreadsheet, and keep it linked (thiere is checkbox in import dialogue). Then on each open it can be updated with new values.

3. Well, this is possible with sheets also, using macroses. Though I think it’s not worth the effort.

[tryingtoleavethisdumsite]

Thank you LogicDemon, for taking the time to give me so much information. I will consider the option of using Google Forms, and also try to get my head around the structure you describe.

If I create a number of tables as you describe, can I use a single form to input data into fields selected from different tables?