Hello all,

I would like a database for my local school (no, I am not a student but a 43 year old army officer) to better organise their events and changes to their timetable etc. The schema I've worked out looks quite complicated, can you think of a better way of doing this ? This is my first database and I picked up the 'How To' book 2 weeks ago.

I have a real problem over how one records pupils against positions given that each team for each sports has different positions and places.

Some assumptions:

Each sport is unique.
Each Pupil is unique.
Each school is unique.
Each teacher is unique.
Each team is unique but a sport may have many different teams.
A team can have many pupils playing in it.
pupils cannot play in more than one team at the same time, though they may play in more than one team on one day.
A fixture list can have many matches but each match is unique.

I would be grateful for any help you may be able to offer. I'm just picking up the language....

any chance you could post whatever is in that attachment in plain text rather than some evil corporation's proprietary format?

Sorry, big faux-pas.

Thanks for your help.

tl;dr

a quick skim showed that the parent entity has a child_id attribute of key

implication is that a parent cannot have more than one child

fix all those errors first

Thanks chap. Sorry to be slow, but could I not input more than one Pupil_ID for each parent ? So each child is unique to that parent. I don't doubt what you say, I just don't know how to fix it !

each chil is unique to a parent is good

but a parent can have more than one child, so you'd use a linking table

but not have the childid in the parent table

Rudy, do we need to have The Talk?

no, you go ahead and have it with mere1, and i'll listen in...

Hi guys, new member here. I have some experience in this particular field, and a bit of theoretical background in database theory, so I hope I can be of help.

This looks like an Access database, based on how you named your data types. You haven't indicated your primary keys or unique constraints, so I'll just make my own assumptions. Please correct me if I'm wrong.

Your Pupils table contains a Year field. Are you going to have a separate record for each year a pupil is in the school, with the surname/first name/photograph duplicated? A pupil will have a unique ID for each year, and you won't be able to identify the same pupil in different years, e.g. to compare marks or derive aggregate information. You could split this table into Pupils and Pupils_Years, allowing you to relate a single Pupil_ID to multiple years, and place general or year-specific information in the appropriate table.

Your Parents table contains references to the Pupil_ID, meaning a parent will be related to a single child and school. You also show a Parents_Pupils_ID table, which relates parents and children. Are you recording the same information in two different ways here? I would recommend you choose one way, depending on how you want to handle this relationship. Perhaps remove the Pupil_ID from the Parents table, and rely only on Parents_Pupils_ID - it's more general.

Still in the Parents table, your telephone numbers are typed as numbers rather than text. Text generally works better if you're interested in a sequence of digits rather than a numeric value. Moving on, why do you have School_ID in there? Isn't the school more appropriate to record on the pupil's record rather than the parent's?

What is the Parents_Puils_School_ID table for? It has a Parents_Pupils_ID field, where does this value come from?

Like with the relationship between parents and pupils, you've got a general Teacher_School_ID table which I think subsumes the Teachers table's Teacher school field. How are you going to use it? One school per teacher, many schools per teacher?

In Parents and Teachers, you've got My Team / My Matches / My seasons. Are these meant to be features rather than fields?

In Sports_Pitches_ID, I think making Sports_ID an autonumber is a mistake. You probably meant to relate Sports to Pitches via their IDs.

In Team_Sports_Positions_ID and Team_Pupils_ID, you may want to split the teams and positions into separate tables. Repeating Position and Shirt number fields like that makes things difficult later on.

In your Fixture List, you have a reference to a Team_Sports_ID. Where is that coming from? Also, is it correct to define seasons per sport and team? Why do you have Match_ID in there?

In Matches, Season_ID identifies the team/sport, and I assume School_ID refers to the opposing team's school. You've got Team_Pupils_ID in there, which links in a specific pupil - why? Perhaps that should be used to indicate the Captain, rather than a text field? What's the Teacher_ID for? About the position/wins/draws/losses - putting running totals in the match table may not be the best idea, but it's not the worst idea either. You should understand the consequences of doing so. The Upload options look more like feature requests than fields - for the report field you can use a Memo, while the photos are likely to be a separate table.

Your Fixture list and Matches tables have Active / Archive fields. These aren't ideal in practice. Using explicit dates usually works better. Note that tables which don't contain or link to a year or time period is recording time-independent information. Will your sports always be linked to the same pitches and teams and will teams always consist of the same pupils, or do you intend to record just the current situation? Your matches are linked to teams, what happens when the current team arrangement changes?

Finally, about table and field names. Avoid spaces and punctuation except underscore. Pick names that describe what a field or table is intended for, rather than just repeating the name of the foreign column - case in point being the School_ID in Matches which I would name Opponent, if that is indeed what that field is for. If you properly define your foreign key constraints, the linkage will be in the database anyway.

Very grateful for your time. will go through these points in detail.

Pupil Year field - Good point. Removed.
School ID - Agreed.
Phone numbers - thanks.

Parents_Pupils_School_ID comes from a combination of Parents_Pupils_ID and SChools_ID in order to ensure a unique record of a parent, with a pupil to that school ?

My Teams & Seasons: Got it.

Teacher / Schools: No, you're right. The aim is one school per teacher. I'll marry them up in the Teacher_School_ID

Pitch ID : Thanks.

Too late to crack the teams tonight. I've been going round the houses for ages on that one.
Thanks again for help.

If I'm reading your text file correctly, Parents_Pupils_ID is a table containing Parent_ID and Pupil_ID fields. If so, Parents_Pupils_School_ID should be a table consisting of Parent_ID, Pupil_ID and School_ID, unless you want to define a Parents_Pupils_ID autonumber field in your Parents_Pupils_ID table.

I would relate the pupil to the school, and the parent to the pupil, as two separate relationships.

A useful way to analyze it before building tables is to write out pairs of statements to describe your requirements, e.g.:

• each pupil has zero or more parents
• each parent has one or more pupils