Designing database for school

Trying to call a student I found that my little school doesn't even have student ID numbers! This got me thinking that we should have a database linking students with courses - and courses with students as well as other tables such as course fees, Evaluation reports (linking to grade table), teachers, exam results, issued certificates, receipts etc.

I have made only one simple relational datababase some 10 years ago in high school, so while I think I could still link one student and one course, I don't know how to it works with two courses - especially the other way round... :-o I expect - hope - the school to grow in a future so I hope to get the design robust enough for tables to be added later (e.g. salaries).

I have Office 2000 at home but the school uses Office 2010... I wonder if I could design at least a draft in Access 2000 to show the principal/director how it works and the benefits it would bring. No more blank face from the receptionist when I ask how many students are there in a course I don't teach. (But I need to know.)

Obviously, if you work in a education or similar organisation, any advice for creating tables, what info/ fields I should have and how to determine primary and secondary keys etc, are welcome.

 

OK, I've found some tutorials to freshen my mind - and learn how to do it in 2007. ( I also have 2007 in my laptop but I never use it because I'm too lazy to learn it and it's so resource hungry that my old laptop can hardly cope)

I still welcome advise what information etc I should include. I'm not admin staff, I'm just a teacher in a 3rd world country, trying to help.

 

Is Office 2000 installed on a desktop system or some computer other than your laptop? Have you thought about installing Office 2007 on the computer you have Office 2000 installed on? Office 2007 is much closer to Office 2010 than Office 2000. The Microsoft software single-user license agreement allows installing Office on a desktop system AND a portable computer. So, if you're using a desktop system for Office 2000 and that computer meets the requirements for Office 2007, I would make the switch.

Regarding building of relational tables in Access, a Google search on building relational tables in access will yield many references. I'm familiar with Access but don't feel comfortable trying to give detailed advice on this topic.

Edit: you might also find an Access user forum, such as http://www.utteraccess.com/forum/forums.html, helpful.

 

I have never used Access, so I cannot give you any advice on it.

However, I have just downloaded and installed Microsoft SQL Server 2012 Express, and it is really easy to use!

You can use Excel sheets with your data, then use the Import/Export Data tool to upload it into database tables.

SQL Server Management Studio allows you to write SQL queries, views, joins, etc to extract the information you need.

It's quite easy to use, but you do need a basic knowledge of relational databases and SQL, which I understand from your first post you have.

 

But you were not asking what database to use, your questions was about linking.

Let's start with the two tables you imagined: students, and courses.

students columns

• student_id int primary key,
• student_name text,
• student_address text,
• student_tel text,
• ...

courses columns

• course_id int primary key,
• course_name text,
• ...

course_students columns

• student_id int,
• course_id int,

You may want to add constraints, to make sure the student_id and course_id exists, but this can be added later.

You can also create a teachers table, with a course_teachers table. All can be added gradually, once you have a working base.

And of course at some later stage you want to actually write an application, so you don't have to do all the work in SQL.

 

This is the type of thing that I used to do as a programmer / designer, but I did that by writing applications rather than
using end user development software.

After doing a little research and without having used any of the products, my recommendation is LibreOffice Base which
is part of LibreOffice Productivity Suite.

The latest version, 3.6.1 Final, is available for download at Major Geeks, however that is not the version I recommend.
On the LibreOffice Release Notes page you can see that an earlier version, 3.5.6 Final, is the 'conservative' release recommended.

The LibreOffice System Requirements are lower than
Microsoft® SQL Server® 2012 Express System Requirements
and therefore will run effectively on more computers and older systems.

Base - A Powerful Manager for All Databases

Maybe I'll get a chance to draw a diagram that shows how to set up linkages between data files (tables), which is necessary to
create one-to-many relationships. Managing those links properly is critical to ensure proper function of a relational database.

 

Regarding LibreOffice Base, that is a good, light-weight alternative to Access or Microsoft SQL Server. However, in the current release, the SQL specs are rather old; e.g. it does not support OUTER JOIN. (You probably won't need that for your school database.)

 

Can i make a suggestion? Dont do this, unless you plan on being involved with this school for 10+ years. What happens when your system starts being used, and all your time goes towards fixing/maintaining it. What happens when all these records get damaged or messed up when people started relieing on them? Its a noble idea, but creating your own personal solution might just cause more issues down the road. Keep this in mind, and good luck.