Databases...

[ben0]

Hey guys is there any Mysql/Database guru's here?

Need a little help on a table design. I though i'd see if there is anyone with DB knowledge before I put a really really long winded and confusing post!

Thanks.
:confused:

[kiwi]

I can help but I will not be reading a long post :)

[ben0]

Excellent, thanks, I will try to keep it brief! ;)

Basically I need a database to hold student achievements in basic numeracy. There are 3 categories, Stage 1 with about 15 levels, Stage 2 with about 10 levels and Stage 3 with about 18 levels.

Each session (could be upto 5 a day) if the student meets one of the levels they get a tick, doesnt matter if they dont achieve any level. At the end of the term we need to count how many of times they completed each level. It could only be that for 5 sessions a day, everyday a student only ever achieves one level. I need to record the teachers name, session date and level achieved.

Its gotta be done with a web front end, so was gonna use mysql with php, got some experience but designing tables aint my strong point :)

What do you think would be the best way.
TIA

[kiwi]

Well, I might not understand now all details that you need (kinda late here :D) but basically you need separate tables for students and teachers that hold their ID, name etc. Can also be person table and another status table (teacher, student) that you link together with foreign keys.

Another table for levels and stages (if you plan more stages then another table for stages and again link together with foreign keys)

And then you also have sessions table where you record teachers name (linked as foreign key from teachers/persons table), session date, student and level achieved (again linked from levels table), etc. Then you simply count records/rows in the sessions table depending on parameters you need

I don't know if you got an idea or not but it's hard to explain briefly and better to explain verbally :)

At least I hope you are familiar with basic terms such as rows, columns, primary/foreign keys etc?

[ben0]

Thanks for replying, yeah rows, columns etc - all cool with, foreign keys - yeah this one gets me, but Ive gotta book on DB's so Im gonna go and have a read! lol, I've not really done much with linking tables and such!! Still a noob with Mysql! Trying though!

You mind if I post back with any queries I have? I thought you did a pretty good job with that explanation considering 8 hours ago I was snoozing...What TimeZone you in?

Thanks again,
ben0

[kiwi]

Sure, I am in GMT+2 timezone, that's overseas in Europe :)