OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Help with designing database for a teacher

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
betes
Newbie
Newbie


Joined: 30 Apr 2008
Posts: 2

PostPosted: Wed Apr 30, 2008 4:15 am    Post subject: Help with designing database for a teacher Reply with quote

Hi all-

I'm not sure if this is an appropriate topic for this forum, as its more a general question about database design than a question about OOobase specifically, so ignore or delete this thread if necessary. Now my question:

My wife is a montessori teacher. In montessori, each child has an individualized "path" trough a series of established "lessons". The lessons are arranged in four categories and in specific order within each category (e.g. Category A, lessons 1-10; Category B lessons 1-10; etc). She wants a database that will help her keep track of which lessons she has given to which child, and which will allow her to sort it by category, date, etc. That part is easy. It will just require one table with fields "child name", "lesson given", "lesson category", and "date".

!!Here is the question:

She would also like to easily be able to view the next lesson each child should receive in each of the four categories. For example, if she saw that a child named joe didn't have any work at a given time she would like to be able to quickly pull up a saved query on her computer that would tell her that Joe needs "Category A, Lesson 4; Category B, Lesson 7; etc". (In case your wondering, there are far more than 10 lessons per category and they don't have simple names like lesson 7. If they did she wouldn't need this database!) If you know the answer you don't need to read any further, but below are my thoughts about how it would work, although I haven't been able to make it work:

She could make a table for each category listing the lessons in order and then relate the lesson fields from those table to the lesson fields in the main table (the one where she enters every lesson given). She could then some how query what the last lesson given (by date) to a specific student was in a specific category and have it output the next lesson in that category's lesson table. So thats my idea, but I don't know if its possible and how to execute it. If you've read this far, thanks! If anything is unclear let me know.
Back to top
View user's profile Send private message
Voobase
OOo Advocate
OOo Advocate


Joined: 21 Nov 2007
Posts: 400
Location: Australia

PostPosted: Thu May 01, 2008 2:29 am    Post subject: Reply with quote

Hi Betes,

In base you would probably do it like this...

Create 4 tables... (you would use design view, not wizard to do this)

The first table contains an auto incrementing Primary Key and the fields for the student's personal details like name etc.

The second table will store all the lessons that all the students have completed and the date they were completed. It also needs an auto incrementing primary key along with an extra field for storing the primary key value from the first table. This is known as a foreign key and will be the means to identify which of the "completed lessons" belongs to which student. It will also need another two foreign key fields to store the primary key values from tables 3 and 4 which will be the lesson category and lesson name information. You may also want a couple of text fields in this table to store the actual category and lesson names that relate to each of these foreign keys, but this is for readability only and won't be involved in the actual operation of the database. This second table, along with storing category and lesson information for each student, will also act as a cross reference table so later you can operate queries on it to determine what students have completed which lessons etc.

The third and fourth table will be responsible for supplying the list to a couple of dropdown listbox's that will be located on the main work form....

The third table will be small and will contain an auto incrementing Primary Key and the name of the lesson category. In your case there will be 4 rows of lesson categories involved.

The fourth table will contain all the lesson names and will also have an auto incrementing Primary Key along with a field for a foreign key. The Foreign Key field will hold the Primary Key from table 3 so that each of the lesson names can have the correct lesson category associated with it.

In base you will need to create at least two separate "writer document" forms (data entry pages), the first for entering the lessons taught against the student (mainform - subform) and the second for populating tables 3 and 4 with the lesson category and lesson title information. This is so the information can be put into the tables in a related fashion. The forms wizard in base will help you create these forms fairly easily as it will walk you through creating a form that contains a mainform and a subform such that they are related in a "one to many" fashion.

The main work-form (data entry page) will actually contain a mainform and subform linked to operate as a one to many relationship. The mainform will show information about the student and should display individual fields with the relevant information in them. The subform should be a "table grid" type of form so that you can see several rows at once. The subform should be linked to the mainform with its foreign key field being fed the primary key value from the mainform when ever a new record (row) is put in the subform due to a student completing a lesson. The subform (table grid) will also have two list box's hanging off it.

The listbox's will need to be controlled by a macro so that when a lesson category is selected in the first listbox it automatically narrows down the lesson names list in the second listbox to only display the lesson names associated with the category selected. The macro will also copy the actual text across to the subform for readability. It is a property of listbox's that they can automatically put a boundfield value in the table that the listbox's are attached to, so the primary key from the lists source tables is also copied to the subforms table helping to turn it into a cross reference table, as it is associating tables 3 and 4 with table 1 through table 2.

The other form is the easy one. All it is there for is to populate table 3 with the lesson category information and table 4 with the lesson name information but in a "one to many" related manor. Use the forms wizard to create this one as you will specify table 3 as the content for the mainform and table 4 for the subform. When the wizard asks, you will need to specify that the Primary Key in the table 3 mainform is linked to the foreign key field of the subform.

It is only once you are at this stage that you can then think about designing any queries to look at data from a different direction. You would simply design the queries in query design view and then create another form to be able to display the results of the query.

OpenOffice.org Base is great after you have spent some time with it and it is beginning to make sense. It may be a bit of a learning curve though, especially when it comes to building your macro for limiting the list in the second listbox. There are several examples of how that is done on this forum, so you would need to go looking and learning.

I hope this helps you by describing how it would be done in Base.

Cheers

Voo
Back to top
View user's profile Send private message
betes
Newbie
Newbie


Joined: 30 Apr 2008
Posts: 2

PostPosted: Thu May 01, 2008 8:21 am    Post subject: Reply with quote

Voobase-

Thank you so much for the time to make this very detailed post. I've read through it and have a basic understanding. I'll try to start trying to put it all together soon. Luckily Base is the first database program I've used, so at least I won't have to re-learn anything from another program.

Again, thanks for this and I'm sure my wife sends her thanks as well

-Betes
Back to top
View user's profile Send private message
Voobase
OOo Advocate
OOo Advocate


Joined: 21 Nov 2007
Posts: 400
Location: Australia

PostPosted: Thu May 01, 2008 5:35 pm    Post subject: Reply with quote

No worries, It won't be too long before Base starts making sense to you.

Quote:
Luckily Base is the first database program I've used, so at least I won't have to re-learn anything from another program.


Your lucky because a lot of people get caught up coming over from Access, expecting it to be the same but finding it isn't. With base you can get simple one to many relationships done just with the forms but invariably you will need to learn about macros too. Luckily there are many macro examples on this forum.

Here is a link to where I listed the doco that helps me along. There may be some more basic stuff out there that I haven't listed though.

http://www.oooforum.org/forum/viewtopic.phtml?t=70788

When it comes to narrowing the list of a listbox by another, here is an example by Drew Jensen: (look at onStatusChange_STKOUT_Category and limititems sub's)

http://www.oooforum.org/forum/viewtopic.phtml?t=57424

And I have got them working in an example I put up for someone here:

http://user.services.openoffice.org/en/forum/viewtopic.php?f=13&t=5234&sid=89f74b53a1fb73e0a82de90acbced40e

Anyway, Have Fun Smile

Voo
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group