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

Suggestion with database design

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


Joined: 08 May 2008
Posts: 3

PostPosted: Thu May 08, 2008 9:58 pm    Post subject: Suggestion with database design Reply with quote

Hi, dont know if this topic is accurate in this forum, but I will try my luck.

I need to create a database for an academy. We conduct lessons in schools, but also private tuition. My task is only arrange the schedule, nothing related to accounts.

So I think I need 3 main tables: Teachers, Schools, Pupils

Then I need also a table for the private lessons (stating with teacher, pupil, day, time) and another for schools lessons.

Mainly, the 5 tables I think should be enough are:

TEACHERS (with all his personal data and so)
SCHOOLS (name, address, phone, contact teacher, ...)
Groups (as every school can have different groups, different timing and teachers)
PRIVATE PUPILS (particulars of student and usual teacher we send him)

Lessons..... This is my problem! As I would like to include here lessons in schools and private (but dont know how to do it)

Ok, my problem is to do queries including data from this 2 different tables. I mean, to create a query showing all the lessons for a teacher, dont mind if private or schools one.

I have been thinking that my life would be much easier if i put schools and pupils under same table, and then create an extra field as a parameter that identify if a customer is a school or a pupil, but dont look too nice (however perhaps this will be my final option).

Can you make me any suggestion??
Back to top
View user's profile Send private message
keme
Moderator
Moderator


Joined: 30 Aug 2004
Posts: 2910
Location: Egersund, Norway

PostPosted: Fri May 09, 2008 12:09 am    Post subject: Reply with quote

The bad news: This is not a trivial task. Do not attempt to build a school scheduling administration software yourself unless you are a seasoned software and database developer. Making the basic database for the "appointment scheduling" type of tasks is enough of a challenge, considering all the dependencies and constraints involved. Schedule planning is even harder, requiring that you are familiar with some optimizing algorithms.

The good news: There are ready made applications, some very affordable, others commanding a high price, that integrate all the tasks indicated. Search for School Schedule Software on the web, using your favourite search engine. (I briefly checked out Lantiv and Mimosa at a previous occasion, and they both looked promising. Choices were made over my head, though, so those tests were terminated prematurely.)
Back to top
View user's profile Send private message
Asdracles
Newbie
Newbie


Joined: 08 May 2008
Posts: 3

PostPosted: Fri May 09, 2008 12:31 am    Post subject: Not so easy Reply with quote

Believe me, after one year and a half in this company, I know that NO SOFTWARE in the world can help us.... this is too crazy, believe me Smile. I dont know a too profesional tool, just one that I can modify daily if needed (and believe me, really needed)

Company was working with an Excel file!! so upgrading to Database system will make things easier (guy doing that work has been doing that for years, and was the only one able to understand that chaotic file)

I can think in sorting the matter by "not so professional ways" like a manual copy-paste after the queries, but just thinking about the option to manage that.

I repeat the main problem, if I create a query for getting the list of lessons in schools for a teacher A, no problem. If I create a query for getting the list of private lessons for teacher A no problem.

The problem is getting a list where I can put those data together.

Thanks anyway for your answer!!
Back to top
View user's profile Send private message
Mark B
Super User
Super User


Joined: 16 Feb 2007
Posts: 852
Location: Lincolnshire, UK

PostPosted: Fri May 09, 2008 12:48 am    Post subject: Reply with quote

Hi

The secret is to have a third table that links the teacher to the lesson. You'll also need a unique id for each teacher and each lesson so that the set up is something like:

  • Table Teacher (id,surname,firstname,etc,etc)
  • Table Lesson (id,room,start_time,etc,etc)
  • Table Teacher_Lesson(teacher_id,lesson_id)


I won't lecture you on OOo Base not being the right database for you, especially if you're going to have more than one user, and that you should be looking at MySQL Wink

Mark
_________________
Mark B's Articles
Back to top
View user's profile Send private message Send e-mail Visit poster's website MSN Messenger
keme
Moderator
Moderator


Joined: 30 Aug 2004
Posts: 2910
Location: Egersund, Norway

PostPosted: Fri May 09, 2008 1:39 am    Post subject: Reply with quote

OK, then to simplify things a little, you need to make them more complicated...

A few starting points:
Forget the separate tables for private pupils and groups. Use one student table and one group table. The teachers teaches groups. The private pupil is a group of one.

Do not store starting/ending times in the group table. Define time blocks, keep them in a separate table, and use a similar table to Mark B's suggestion for linking groups and time blocks (Actually this table should probably link group, teacher, classroom and time block together)

To make a reliable database structure, there are a few steps you need to go through:
  • Determine what information you have available to put into the database
  • Determine what info you need to get out
  • Define tables.
  • Normalize (this is a formalized process that may cause modification of table structure, adding or removing tables, and more)


Basically a database is better for manipulating data structures like this than spreadsheet software is, but if it's not properly made it can be a mess, and a mess in a database is often worse than a mess in a spreadsheet...

Furthermore, as Mark B suggests, OOo Base has had some stability issues. I haven't tested extensively with the latest versions, but I would recommend frequent backups, and do not access the database file simultaneously from different locations (neither over network nor by two instances of Base on the same computer.)
Back to top
View user's profile Send private message
Asdracles
Newbie
Newbie


Joined: 08 May 2008
Posts: 3

PostPosted: Fri May 09, 2008 8:23 pm    Post subject: Reply with quote

Thanks again Keme and Mark B for your comments.

I would like to tell you that Im not an expert but not a beginner with databases, but I had never faced this concrete problem, and Im more familiar with Access and only quite new with OpenOffice (specially Base)

Also that my academy is much more complex. There are not time blocks. Every lesson is very flexible, can move to another day, to another hour, not same length, .... Thats why I need the lessons table with the timings (really not schedule, but day by that matters!) and thats why the previous guy doing this was working like a puzzle with his Excel worksheet.

No needed SQL, as the database will be only for my use, and teacher will be sent a concrete schedule but they wont have access to the database.

I will really think seriously in creating a school called "Private pupils", and then treat identically every pupil or every group in school. Something like this

Teachers (Teacher_ID, Personal Data)
Schools (School_ID, Address, Contact_Data, ....) (Here I would include a virtual school called Private Students)
Groups (Group_ID, School_ID, CONTACT DATA?, ....)
Lessons (Lesson_ID, Group_ID, Teacher_ID, Start_Time, Length)

I dont need a proper students table, based I have stored my private students in the Groups. Im not interested at all in the students that Im teaching in schools. Customer will be the school, and only trainer is interested in the list of pupils, not my company.

So now my only problem is to avoid redundant information. I mean, with this structure I need to store in the Groups table all contact data for private students, but will be useless to store contact date about Groups in Schools, as I have all this data in the Schools table.

Also I can think about putting all the data about schools in the Groups table, but then I would repeat lot of information (and in fact, the Schools table would be useless).

Any final help??

Thanks in advance!!!
Back to top
View user's profile Send private message
Voobase
OOo Advocate
OOo Advocate


Joined: 21 Nov 2007
Posts: 400
Location: Australia

PostPosted: Sat May 10, 2008 3:59 am    Post subject: Reply with quote

Hi Asdracles,

Thought i'd crash in and give my 2 cents worth. Being fairly new to Base I can outline the ways I have found things work with Base and give some indication of what may be ahead for you.

When you get to building your database you will find that the forms you design will consist of a mainform and subform. This allows for a 1 to many type relationship to exist when entering or viewing your data. To achieve this you "link" or "join" your forms in the subform properties by relating the Primary Key of the mainform to the Foreign Key field in the table that the subform is based on. This is one of Bases way of striking up a relationship between tables. You can extend this to sub-subforms or have several independent mainform and subform forms in the one document. The other way I know to bring data relationships to a table is to use a listbox, which can be placed on your main or subform. They are based on a separate table (or "query" or "view table") and provide a bound value, normally the Primary Key, to be placed in the table of the form the listbox sits in.

In Base if you are basing forms on "queries" or "view tables" and they are gathering their info from more than one table then they are not updatable. This is not too much of an impediment as the "queries" and "view tables" still run behind the scene and can be accessed with macros or brought into your document via another form to display data whilst you enter data into the tables that supply the query.

In your situation it sounds like the table which will be doing most the work is the "Lessons" table. The "Lessons" table would also hold relations to the "Site", "Location" and "Pupil" tables etc. In Base you may find that you need to build several forms which all use this "Lessons" table in the subform position. This way, with the 1 to many feature the subform allows, you could display all the lessons that a teacher has assigned to them or all the lessons that fall under your "Lesson_Category" etc. ("Lesson_Category" would contains data like "Grade 1 lessons" and "Groups" (as suggested earlier) or things like "Maths" and "Science", whichever suits your requirements). Private students could be include as another "Lesson_Category" (as suggested earlier). This way you may have a category such as "private students grade 1" and in the subform below would display all the lessons in that category.

To relate other tables data into the "Lessons" table you would simply put some listbox's on the subform. You may even be able to get fancy when generating the list for the listbox and have it based on a "query" or "view table" (rather than a data table) such that the list reflects what actual resources are available. One listbox can be made to narrow the list of another by writing some macro's. This might help in some situations such as if viewing the "Lessons" table and wanting to change the room number. The "Site" (school, floor level or private) would be selected in the first listbox which narrows the "Location" listbox to only show the relevant room numbers at that particular site. A private address would be treated as a "Location" in this situation.

It is a shame that you don't think you will be able to provide consistent "time blocks", as using these in your mainform and the "Lessons" as the subform would show all the lessons that fall within a particular period. You might be able to find another way of doing this by having the day itself as the time block and using "filters" modified by macros or else use a "query" or "view table" version of the "Lessons" data (if you are only viewing). A time field in a separate form and table (but in the same document) might be able to modify the data for the "filter", "query" or "view table".

For handling the pupil information the Primary Key of the "Lessons" table can be linked to a "Pupils_Lessons" table (in a subform to the "Lessons" form) to display only the students involved for a particular lesson. To populate the "Pupils_Lessons" table a seperate set of forms would be used.

You will have to build a separate set of maintenance mainform - subform forms which will be required for setting up some of the constant information in tables that have relationships with other tables. Information such as what pupils attend which lessons (for the "Pupils_Lesson" table) or which teachers are qualified to teach which lessons, if this relationship is required. Also anywhere a listbox that narrows another is used, the relationship can be mimicked in a mainform - subform setup so the information would be entered this way.

Here are some links to some recent posts I have been involved in, also with an education theme. The first was to advise how one would do a database in Base and the other gives an example of using one listbox to narrow another.

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

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

Cheers

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