If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Student Sport Database - advice on my design, please?
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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200903/1 |
#2
|
|||
|
|||
Student Sport Database - advice on my design, please?
On Sun, 22 Mar 2009 10:35:23 GMT, "biganthony via AccessMonster.com" u31673@uwe wrote:
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) ); |
#3
|
|||
|
|||
Student Sport Database - advice on my design, please?
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" u31673@uwe wrote in message news:937541e6cb1b0@uwe... 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200903/1 |
#4
|
|||
|
|||
Student Sport Database - advice on my design, please?
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 tina wrote: 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 http://www.accessmonster.com/Uwe/For...esign/200903/1 -- Message posted via http://www.accessmonster.com |
#5
|
|||
|
|||
Student Sport Database - advice on my design, please?
On Sun, 22 Mar 2009 22:41:05 GMT, "biganthony via AccessMonster.com" u31673@uwe wrote:
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? |
#6
|
|||
|
|||
Student Sport Database - advice on my design, please?
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) |
#7
|
|||
|
|||
Student Sport Database - advice on my design, please?
you're welcome
"biganthony via AccessMonster.com" u31673@uwe wrote in message news:937b97fc1e185@uwe... 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 tina wrote: 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 http://www.accessmonster.com/Uwe/For...esign/200903/1 -- Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|