Database Relationship help

[NicolaCassidy]

I was wondering if anyone could help me with a query. Basically I am doing (or trying to do) a small database to track childrens progress in my mums primary school. I'm doing this for free as the budget is rubbish for this type of thing and i dont have enough knowledge to ask for payment anyway! I want to get it right from the start and I think if I get the relationships right initially then I can complete the rest of it on my own. I did this at university (normalisation etc) but ive not used it since i left in 2002 and so have practically forgotten everything i ever did.

Basically, the child comes into the school and is predicted a level in maths, reading and writing. Then in the october, feb and july of each of the 2 years they are there, they are given actual assessment levels. They are also given a prediction level at the beginning of year 2.

So far I have one table with student no, surname, first name, gender, ethnicity, year group and SEN(special educational needs) - with the primary key being student no.

This is where I get stuck - do I go for a maths, reading and writing tables and split it that way - or on an assessment basis, so prediction yr1, october yr1, feb yr 1 etc etc. Or is this completely wrong? Am sooooo confused, so any hel pwould be greatly appreciated

Thanks
Nicola

 

[confused]

Im not sure exactly what you mean, I didnt understand a lot of your post ! but from what I can gather that you want to do, you could create a releationship from any other table and use the student ID as the primary key in each case, the relationship could include both the prediction year table and the subject table with no problems.

Hope that makes sense!

You will get more help here http://www.access-programmers.co.uk/forums/ than I can offer.

CALV

 

[NicolaCassidy]

Thanks calv - and i'll take a look at that forum as well

Nicola

 

[DotNetWebs]

It's a bit early in the morning for me but I think I know what you mean.

I would use 4 related tables:

Students: primary key student id

Subjects: primary key subject

Assessment Period: primary key assessment date

Assessments: Primary Key IDENTITY (auto increment), Foreign Keys student id, subject, assessment date

This would allow you to have a table with rows like this:

Student1 - Maths - October 06 - Grade A
Student2 - Reading - February 07 - Grade B
etc..

You could then filter it by Subjects / Assessment Period to show a report like:

Maths October 06:

Student1 Grade A
Student2 Grade B
Student3 Grade A
etc..

Or by Students / Assessment Period to show a report like this:

Student1 October 06

Maths Grade A
Reading Grade B
Writing Grade B

Or by Students / Subject to show a report like this:

Student1 Maths

February 06 - Grade B
October 06 - Grade A
February 07 - Grade A


Or any other combination..

Also if you where using fixed grades like I have in this example I would store them in their own table as well. If you use percentages or any other variable then I would store them in a field in the Assessments table.

 

[NicolaCassidy]

Thanks so much for that! I think I will go with the structure you have suggested so thanks again

 

[WarrenS]

Is access compatible with web programming? Could i use it as a db for information?

Sorry, just getting started on web site stuff and haven't a clue.

PM's with hints and tips and where/how to learn are more than welcome

 

[Richard Conyard]

http://www.sitepoint.com <-- lots of web dev stuff there.

 

[mattk]

Is access compatible with web programming? Could i use it as a db for information?

Sorry, just getting started on web site stuff and haven't a clue.

PM's with hints and tips and where/how to learn are more than welcome

In reality, no. Access isn't really a database, it's just a collection of realitional text files. It can't support more than a couple of symultaneous connections and you end up with all kinds of locking issues.

You're best bet is mySQL if you want to programme php or SQL Server if you want to go the Microsoft route.

 

[Richard Conyard]

Matt - for the purposes of web it should be okay though for simple stuff. Using ADODB I was able to simulate a few hundred simultaneous connections.

 

[mattk]

Actually, SQL Server 2005 Express Edition is free - so I'd definitely use that in perference to Access

 

[climbingmerlin]

Just to point out about sql server 2005 express, is that there is a cap on the database size limit... I think it is 2GB. Just might be important if you are storing a lot of data.

 

[mattk]

Fair play, I wasn't aware of that. TBH, I wouldn't fancy trying to store 2Gb of data in an Access file!

 

[climbingmerlin]

True... neither would I. I have had to deal with a 700mb access file once, with an application. It was a nightmare, the program kept crashing, database kept getting currupted all the time

Managed to change the application to use MySQL and all those problems went away...

 

[DotNetWebs]

I agree with Mattk. SQL Server 2005 Express edition is a better option. The Database Limit is actually 4GB

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/sseoverview.asp

One of the good things about Access is that it includes wizards to upgrade the databases to SQL Server. You can therefore start off small with Access and 'scale up' to SQL Server when necessary