Database Design for a School

[FallingToaster]

I'm designing a database for a non-profit School. What they need is a solution that will store Students (Name, address, so forth...) and also the student's schedules.

I have planned the database to be something like this:



Table_Students (Fields: StudentID, Name, Address, Class1, Class2, Class3....ClassX)

Table_Class (Fields: ClassID, ClassName, Instructor, TimeStart, TimeEnd)

What I figured I'd do is create a relationship between the two tables along the ClassX fields and ClassID field. I figured that this would let me input each student's list of classes into their individual records, where I only use as many of the ClassX fields as I need.

As I have begun attempting to develop this though, it is beginning to feel sloppy and I don't believe it is the most elegant solution.

Tell me: is there a better way to do this?

[RuralGuy]

I would strongly recommend you look at sample db's and templates on the web. I suspect there is something close to what you need and will be better normalized to start. Repeating fields is a dead give away on normalization issues. Think Tall and Skinny rather than Short and Wide.

[maximus]

say that every student is in a particular class so instead of including a Class1, Class2, Class3 ect just include a single field ClassID. Create a separate Table Class with (class_ID ((Pkey)(Autonumber) and Class (Text)) link it to class field in your Table_student using the lookup wizard creating a combo feild.

do the same in your Table_Class link ClassID to new Class table using the lookupwizard.

now the job is simple the ClassID field in the Table_student can easily be linked with ClassID in the Table_Class. To generate a report design a select query with Table_Students and Table_Class.

Table_Students (Fields: StudentID, Name, Address, ClassID)

Table_Class (Fields: ClassID, ClassName, Instructor, TimeStart, TimeEnd)