Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2012
    Posts
    5

    Could you do this better ?

    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....
    Attached Files Attached Files
    Last edited by mere1; 02-26-12 at 14:29.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    any chance you could post whatever is in that attachment in plain text rather than some evil corporation's proprietary format?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2012
    Posts
    5

    Non-evil corp format

    Sorry, big faux-pas.

    Thanks for your help.
    Attached Files Attached Files

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2012
    Posts
    5
    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 !

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Quote Originally Posted by r937 View Post
    each chil is unique to a parent is good
    Rudy, do we need to have The Talk?
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by gvee View Post
    Rudy, do we need to have The Talk?
    no, you go ahead and have it with mere1, and i'll listen in...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2012
    Posts
    76
    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.

  10. #10
    Join Date
    Feb 2012
    Posts
    5
    Very grateful for your time. will go through these points in detail.

  11. #11
    Join Date
    Feb 2012
    Posts
    5
    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.

  12. #12
    Join Date
    Feb 2012
    Posts
    76
    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 ?
    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
    Last edited by reaanb; 02-27-12 at 18:14. Reason: referred to incorrect table in my second sentence

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •