SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Feb 2006
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Loop in a database

    I am very inexperienced in databases and I am currently attempting to design a database and there is one part of the schema where I am unsure how to proceed.

    I need to model schools, pupils and teams. Schools have a number of pupils as do teams.

    I rushed in with the following design but it just seems wrong to me. I don't know much about database design but I don't like the idea of having a kind of loop/triangle in the schema like this.

    School(SchoolID, SchoolName, Postcode)
    Pupil(PupilID, School, Team, Forename, Surname)
    Team(TeamID, School, TeamName)

    I was conscious of having a fan trap of a school has many teams and a school has many pupils.

    Is this bad practice or is it fine?

    Any guidance or related articles on this would be appreciated.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    can a pupil belong to a team that doesn't belong to the same school the pupil belongs to? can a pupil belong to a team without belonging to a school? can a pupil belong to a school without belonging to a team?

    i think your design is fine
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Feb 2006
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thank you.

    can a pupil belong to a team that doesn't belong to the same school the pupil belongs to?

    No

    can a pupil belong to a team without belonging to a school?

    No

    can a pupil belong to a school without belonging to a team?

    No

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, in that case, you want this --

    School(SchoolID, SchoolName, Postcode)
    Team(TeamID, School, TeamName)
    Pupil(PupilID, Team, Forename, Surname)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Feb 2006
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for your time.


Bookmarks

Posting Permissions

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