Advice on setting up a database

[Seth Meranda]

This is what I am trying to do:

I have 85 students that can register for classes. I have 25 different
classes. Each student can register for more than one class, in fact they can
register for all twenty-five classes. I want to put the classes they
registered for in a database, and then create an .asp page based on the
database with class rosters.

I will also create an .asp page that I will use to add new records. I also
will use .asp pages to update/delete the records. I want to be able list
each class roster. Therefore, I need advice on what they best way to setup
the database, should I have a column for each class? If so, do I assign
codes for each class and insert the codes into the approriate column? After
creating the DB, I will be able to adjust the .asp pages.

Any advice would be great!

Seth Meranda
web<at>meranda.org

 

[rpw]

No, you DON"T want to put 25 redundant fields into a table. It sounds like you have a many-to-many relationship between students and classes. One student can have many classes and one class can have many students. Therefore, in order to handle this relationship you need a junction table. For example:

tblStudent
StudentID autonumber PK
FirstName
LastName, etc.....

tblCourses
CourseID autonumber PK
CourseName
CourseDescription, etc....

tblStudentCourses
StudentID 'combine this field
CourseID 'with this field to make the PK

The Course table will have 25 records (one for each class), and the Student table will have as many records as there are students. The student/courses table will have up to [25*(# of students)]. You can use a subform to list the student/courses for each student (or the other way around).

Another comment: I know that Access has certain words that are reserved ('date' being one of them) and I know that VBA uses object 'Classes'. I don't know if 'Classes' is a reserved word. So, being ignorant, I chose to use "Courses" instead of "Classes" to avoid any possible naming conflict.

Hope this helps - post back if you have more questions.

 

[Seth Meranda]

OK, that makes a bit more sense. I am still new to the world of databases. I
have a few more questions:

1) When I create a form to insert the information with .asp, I can only use
one table. How do I define which course each student is assigned to? I
understand the tblStudentCouses "joins" the other two tables, but how do
they interact? In other words, how does the database know which records from
the tblStudents are in which course?

2) When I want to display a class roster, which table/column do I pull the
recordset from?

Seth Meranda
web<at>meranda.org

No, you DON"T want to put 25 redundant fields into a table. It sounds

like you have a many-to-many relationship between students and classes. One
student can have many classes and one class can have many students.
Therefore, in order to handle this relationship you need a junction table.
For example:

tblStudent
StudentID autonumber PK
FirstName
LastName, etc.....

tblCourses
CourseID autonumber PK
CourseName
CourseDescription, etc....

tblStudentCourses
StudentID 'combine this field
CourseID 'with this field to make the PK

The Course table will have 25 records (one for each class), and the

Student table will have as many records as there are students. The
student/courses table will have up to [25*(# of students)]. You can use a
subform to list the student/courses for each student (or the other way
around).

Another comment: I know that Access has certain words that are reserved

('date' being one of them) and I know that VBA uses object 'Classes'. I
don't know if 'Classes' is a reserved word. So, being ignorant, I chose to
use "Courses" instead of "Classes" to avoid any possible naming conflict.

 

[rpw]

Unfortunately I have absolutely no knowledge about .asp. However, if asp can use a query instead of a table, then you're in luck. All that you need to do is create a new query in design view. Show the three related tables. Drag the ID fields from the junction table into the grid.....

Hmmm.... You know what, if you change the structure of the junction table just a little so that there is an autonumber PK (instead of a multiple field PK) and index the two FK's so that there are no duplicates, it makes things just a little easier.

Drag the StudentCourseID field into the grid. Then, drag the other fields that are descriptive of the student and the course from their respective tables down into the grid. For example: FirstName and LastName describe the student, so drag those fields into the grid. Do the same with the course description fields.

If asp is a static viewing mechanism, you can convert the query into a make-table query and use the table as the row source for the asp (again sorry for not knowing exactly...) On the other hand, if you will want to filter the records to show only one student or one class at a time and you want the user to be able to select the filter, it's probably better to use a query.

Maybe someone else knows????

--
rpw

OK, that makes a bit more sense. I am still new to the world of databases. I
have a few more questions:

1) When I create a form to insert the information with .asp, I can only use
one table. How do I define which course each student is assigned to? I
understand the tblStudentCouses "joins" the other two tables, but how do
they interact? In other words, how does the database know which records from
the tblStudents are in which course?

2) When I want to display a class roster, which table/column do I pull the
recordset from?

Seth Meranda
web<at>meranda.org

No, you DON"T want to put 25 redundant fields into a table. It sounds

like you have a many-to-many relationship between students and classes. One
student can have many classes and one class can have many students.
Therefore, in order to handle this relationship you need a junction table.
For example:

tblStudent
StudentID autonumber PK
FirstName
LastName, etc.....

tblCourses
CourseID autonumber PK
CourseName
CourseDescription, etc....

tblStudentCourses
StudentID 'combine this field
CourseID 'with this field to make the PK

The Course table will have 25 records (one for each class), and the

Student table will have as many records as there are students. The
student/courses table will have up to [25*(# of students)]. You can use a
subform to list the student/courses for each student (or the other way
around).

Another comment: I know that Access has certain words that are reserved

('date' being one of them) and I know that VBA uses object 'Classes'. I
don't know if 'Classes' is a reserved word. So, being ignorant, I chose to
use "Courses" instead of "Classes" to avoid any possible naming conflict.

 

[Seth Meranda]

I realize I am in the learning stages. I have worked with some canned
scripts in .asp in the past and continue to learn as I go.

What Database Normalisation books do you recommend?

--



Seth Meranda
web<at>meranda<dot>org

Hi,

I'm sorry to say but by the sorts of questions you are asking you simply
will
not be able to do this yourself....

...unless you get a few books and read up. First off a book on Database
Normalisation - this will help you understand how you need to organise your
database - i.e tables - what fields should they have, what relationships you
need between them etc...

Next you'll need a book on ASP.
ASP can connect to an Access database via either ADO or DAO so getting
the data you want is not a problem.

However you will need to do some programming to do it in ASP.
If you were simply creating a few forms in Access then you might be able to
get away without learning how to do any programming, but with ASP you'll
have no chance.

What you want to is simple enough but at present you just don't have enough
knowledge to do it. And short of someone here virtually writing the thing
for
you there is no way for you to do so without learning.

Don't take this too personally I'm just trying to lay it on the line for you
and
tell you what you will have to do before you get started.

Regards,
Peter

OK, that makes a bit more sense. I am still new to the world of

databases.

I
have a few more questions:

1) When I create a form to insert the information with .asp, I can only use
one table. How do I define which course each student is assigned to? I
understand the tblStudentCouses "joins" the other two tables, but how do
they interact? In other words, how does the database know which records from
the tblStudents are in which course?

2) When I want to display a class roster, which table/column do I pull the
recordset from?

Seth Meranda
web<at>meranda.org

No, you DON"T want to put 25 redundant fields into a table. It sounds

like you have a many-to-many relationship between students and classes. One
student can have many classes and one class can have many students.
Therefore, in order to handle this relationship you need a junction table.
For example:

tblStudent
StudentID autonumber PK
FirstName
LastName, etc.....

tblCourses
CourseID autonumber PK
CourseName
CourseDescription, etc....

tblStudentCourses
StudentID 'combine this field
CourseID 'with this field to make the PK

The Course table will have 25 records (one for each class), and the

Student table will have as many records as there are students. The
student/courses table will have up to [25*(# of students)]. You can use a
subform to list the student/courses for each student (or the other way
around).

Another comment: I know that Access has certain words that are

reserved
('date' being one of them) and I know that VBA uses object 'Classes'. I
don't know if 'Classes' is a reserved word. So, being ignorant, I chose to
use "Courses" instead of "Classes" to avoid any possible naming conflict.

Hope this helps - post back if you have more questions.
--
rpw


:

This is what I am trying to do:

I have 85 students that can register for classes. I have 25 different
classes. Each student can register for more than one class, in fact

they

can
register for all twenty-five classes. I want to put the classes they
registered for in a database, and then create an .asp page based on the
database with class rosters.

I will also create an .asp page that I will use to add new records.

I
also

will use .asp pages to update/delete the records. I want to be able list
each class roster. Therefore, I need advice on what they best way to setup
the database, should I have a column for each class? If so, do I assign
codes for each class and insert the codes into the approriate

column?
After

creating the DB, I will be able to adjust the .asp pages.

Any advice would be great!

Seth Meranda
web<at>meranda.org