Student Sport Database - advice on my design, please?

[biganthony via AccessMonster.com]

Hello,

My wife has asked me to look at a database she uses at school. It is
currently a databse that was designed years ago that holds student sport
selections. She has to maintain it in a large flat file and too much time is
spent maintaining it. So I said I would give her a hand to redesign it.

Her school year is split into three sport seasons. Some sports occur in two
or three seasons, while other sports only occur in one season. Each student
picks one sport each season.

I was just wondering if some one could have a look over my structure below
and provide any advice.

Thank you,
Anthony


tblStudent
StudentID (Autonumber - PK)
StudentFirst
StudentSurname
DOB
AcademicYear
HomegroupID
HouseID


tblHomeGroup
HomegroupID (Autonumber - PK)
Homegroup
HomegroupTeacher


tblHouse
HouseID (Autonumber - PK)
House
HousePatron


tblSport
SportID (Autonumber - PK)
Sport
SeasonID


tblStudentSport
StudentSportID (Autonumber - PK)
StudentID
SportID


tblSeason
SeasonID (Autonumber - PK)
Season


tblSportSeason
SportSeasonID (Autonumber - PK)
SportID
SeasonID

 

[Michael Gramelspacher]

Hello,

My wife has asked me to look at a database she uses at school. It is
currently a databse that was designed years ago that holds student sport
selections. She has to maintain it in a large flat file and too much time is
spent maintaining it. So I said I would give her a hand to redesign it.

Her school year is split into three sport seasons. Some sports occur in two
or three seasons, while other sports only occur in one season. Each student
picks one sport each season.

I was just wondering if some one could have a look over my structure below
and provide any advice.

Thank you,
Anthony


tblStudent
StudentID (Autonumber - PK)
StudentFirst
StudentSurname
DOB
AcademicYear
HomegroupID
HouseID


tblHomeGroup
HomegroupID (Autonumber - PK)
Homegroup
HomegroupTeacher


tblHouse
HouseID (Autonumber - PK)
House
HousePatron


tblSport
SportID (Autonumber - PK)
Sport
SeasonID


tblStudentSport
StudentSportID (Autonumber - PK)
StudentID
SportID


tblSeason
SeasonID (Autonumber - PK)
Season


tblSportSeason
SportSeasonID (Autonumber - PK)
SportID
SeasonID

Maybe like this:

CREATE TABLE tblStudents (
StudentID AUTOINCREMENT PRIMARY KEY
,StudentFirst VARCHAR (20) NOT NULL
,StudentSurname VARCHAR (20) NOT NULL
,DOB DATETIME NOT NULL
);
CREATE TABLE tblAcademicYears (
AcademicYear VARCHAR (10) NOT NULL PRIMARY KEY
);
CREATE TABLE tblStudentEnrolments (
StudentID LONG NOT NULL
REFERENCES tblStudents (StudentID)
,AcademicYear VARCHAR (10) NOT NULL
REFERENCES tblAcademicYears (AcademicYear)
,PRIMARY KEY (StudentID, AcademicYear)
);
CREATE TABLE tblTeachers (
TeacherID AUTOINCREMENT PRIMARY KEY
,TacherName VARCHAR (30) NOT NULL
);
CREATE TABLE tblHousePatrons (
HousePatronID AUTOINCREMENT PRIMARY KEY
,HousePArtonName VARCHAR (30) NOT NULL
);
CREATE TABLE tblHouses (
HouseID AUTOINCREMENT PRIMARY KEY
,HouseName VARCHAR (30) NOT NULL
,HousePatronID LONG NOT NULL
REFERENCES tblHousePatrons (HousePatronID)
);
CREATE TABLE tblHomeGroups (
HomeGroupID AUTOINCREMENT PRIMARY KEY
,HomeGroupName VARCHAR (30) NOT NULL
,Teacher LONG NOT NULL
REFERENCES tblTeachers (TeacherID)
);
CREATE TABLE tblStudentHomeGroups (
StudentID LONG NOT NULL
REFERENCES tblStudents (StudentID)
,HomeGroupID LONG NOT NULL
REFERENCES tblHomeGroups (HomeGroupID)
,HouseID LONG NOT NULL
REFERENCES tblHouses (HouseID)
,PRIMARY KEY (StudentID,HomeGroupID,HouseID)
);
CREATE TABLE tblSports (
SportID AUTOINCREMENT PRIMARY KEY
,SportName VARCHAR (30) NOT NULL
);
CREATE TABLE tblSeasons (
SeasonID AUTOINCREMENT PRIMARY KEY
,SportName VARCHAR (30) NOT NULL
);
CREATE TABLE tblSportSeasons (
SportID LONG NOT NULL
REFERENCES tblSports (SportID)
,SeasonID LONG NOT NULL
REFERENCES tblSeasons (SeasonID)
,PRIMARY KEY (SportID, SEasonID)
);
CREATE TABLE tblStudentSports (
StudentID LONG NOT NULL
,AcademicYear VARCHAR (10) NOT NULL
,FOREIGN KEY (StudentID, AcademicYear)
REFERENCES tblStudentEnrolments (StudentID, AcademicYear)
,SportID LONG NOT NULL
,SeasonID LONG NOT NULL
,FOREIGN KEY (SportID, SeasonID)
REFERENCES tblSportSeasons (SportID, SeasonID)
,PRIMARY KEY (StudentID, AcademicYear, SportID, SeasonID)
);

 

[tina]

for starters, take the SeasonID field out of tblSports. this is incomplete -
and redundant, because you already have sports and seasons properly linked
in tblSportSeasons. next, if the same students may attend the school for
more than one year, suggest you consider splitting tblStudents into two
tables. something like

tblStudents
StudentID (Autonumber - PK)
StudentFirst
StudentSurname
DOB

tblStudentLodgings
StudentLodgingID (Autonumber - PK)
StudentID (FK from tblStudents)
AcademicYear
HomegroupID
HouseID

otherwise, you're going to have to overwrite the data each year - but
perhaps you don't want to store historical data; if not, then the above is
not important.

also, you need to know which sport for which season, each student picks. so
change tblStudentSports to

tblStudentSports
StudentSportID (Autonumber - PK)
StudentID
SportSeasonID (FK from tblSportSeasons)
AcademicYear

again, if you're not keeping historical data, and plan to overwrite or
replace the existing data each year, then you don't need the AcademicYear
field. if you do track data over multiple years, suggest you also consider a
supporting table listing academic years. it's primary value will be in using
it as the RowSource for data entry/search combobox controls on your forms,
to make sure the academic year is entered consistently, and without typos.
you don't want someone entering 08-09, while someone else enters '08 - '09,
or sometimes 08/09, or 2008/09, etc, etc, etc.

hth

 

[biganthony via AccessMonster.com]

Michael and Tina,

Thank you for your advice, I appreciate it. I will have a go at modifying
my structure and attempt to create the database.

Regards,
Anthony

for starters, take the SeasonID field out of tblSports. this is incomplete -
and redundant, because you already have sports and seasons properly linked
in tblSportSeasons. next, if the same students may attend the school for
more than one year, suggest you consider splitting tblStudents into two
tables. something like

tblStudents
StudentID (Autonumber - PK)
StudentFirst
StudentSurname
DOB

tblStudentLodgings
StudentLodgingID (Autonumber - PK)
StudentID (FK from tblStudents)
AcademicYear
HomegroupID
HouseID

otherwise, you're going to have to overwrite the data each year - but
perhaps you don't want to store historical data; if not, then the above is
not important.

also, you need to know which sport for which season, each student picks. so
change tblStudentSports to

tblStudentSports
StudentSportID (Autonumber - PK)
StudentID
SportSeasonID (FK from tblSportSeasons)
AcademicYear

again, if you're not keeping historical data, and plan to overwrite or
replace the existing data each year, then you don't need the AcademicYear
field. if you do track data over multiple years, suggest you also consider a
supporting table listing academic years. it's primary value will be in using
it as the RowSource for data entry/search combobox controls on your forms,
to make sure the academic year is entered consistently, and without typos.
you don't want someone entering 08-09, while someone else enters '08 - '09,
or sometimes 08/09, or 2008/09, etc, etc, etc.

hth

Hello,

[quoted text clipped - 50 lines]

SportID
SeasonID

 

[Michael Gramelspacher]

Michael and Tina,

Thank you for your advice, I appreciate it. I will have a go at modifying
my structure and attempt to create the database.

Regards,
Anthony

There is a mistake in my design.

tblStudentEnrolments -- NOT tblStudents -- should be related to tblStudentHomeGroups
using StudentID and AcademicYear. This means you will need to add the column AcademicYear
to tblStudentHomeGroups table first.
The key will be (StudentID, AcademicYear, HomeGroupID, HouseID)

You did not explain what these tables represent.

What is HousePatron? Does a House have the same patron every academic year?
What is a HomeGroup? Does a HomeGoup have the same teacher every academic year?
Can more than one House belong to a HomeGroup?
Are students assigned to Houses and Houses assigned to HomeGroups?

 

[biganthony via AccessMonster.com]

Michael,

Thanks for taking time out to help.

Homegroup is the roll class with the same teacher all the time - for taking
the roll each morning. A number of these homegroups make up a sporting house.
About eight homegroups in each of the four houses. (there are 4 houses and 32
homegroups). A house patron is a teacher allocated to a particular house. A
house patron may change if that teacher leaves the school. A home group has
the same teacher until he/she leaves the school. Kids stay in their homegroup
until they leave the school (homegroups are made up of kids of different ages)

 

[tina]

you're welcome

Michael and Tina,

Thank you for your advice, I appreciate it. I will have a go at modifying
my structure and attempt to create the database.

Regards,
Anthony

for starters, take the SeasonID field out of tblSports. this is incomplete -
and redundant, because you already have sports and seasons properly linked
in tblSportSeasons. next, if the same students may attend the school for
more than one year, suggest you consider splitting tblStudents into two
tables. something like

tblStudents
StudentID (Autonumber - PK)
StudentFirst
StudentSurname
DOB

tblStudentLodgings
StudentLodgingID (Autonumber - PK)
StudentID (FK from tblStudents)
AcademicYear
HomegroupID
HouseID

otherwise, you're going to have to overwrite the data each year - but
perhaps you don't want to store historical data; if not, then the above is
not important.

also, you need to know which sport for which season, each student picks. so
change tblStudentSports to

tblStudentSports
StudentSportID (Autonumber - PK)
StudentID
SportSeasonID (FK from tblSportSeasons)
AcademicYear

again, if you're not keeping historical data, and plan to overwrite or
replace the existing data each year, then you don't need the AcademicYear
field. if you do track data over multiple years, suggest you also consider a
supporting table listing academic years. it's primary value will be in using
it as the RowSource for data entry/search combobox controls on your forms,
to make sure the academic year is entered consistently, and without typos.
you don't want someone entering 08-09, while someone else enters '08 - '09,
or sometimes 08/09, or 2008/09, etc, etc, etc.

hth

Hello,

[quoted text clipped - 50 lines]

SportID
SeasonID