I am building a database for an activity society. There are 2 courses in each season, each course has 10 sessions. Students can be old (returning student)and new.

What we want to achieve is to check student information, the student's attendance situation, how many students in each session, etc.

The table I designed is:

1. Student Detail (student info)
2. Spring 2006 Sunday Course (student ID, payment, each session attendance...)
3. Spring 2006 Tuesday Course
4. Summer 2006 Sunday Course
5. Summer 2006 Tuesday Course
.
.
.
(each new course has a new table)

The problem is for each new course will need to add a new table. I just want to know if there is a better way to manage the data. Thanks for you help!

You have set out with essentially pre-conceived ideas.

Start with identifying Entities
Course
Student
etc

Now define the relationships between Entities
A Student may attend Many Courses
A Course may be attended by Many Students


Then define attributes for each Entity
Student name
etc
Identify candidate Primary Keys


Now put the whole lot together in Powerpoint

You will now start to see hwre M:M relationships exist that require decomposing

You will also see what data you will be recording and can decide if it is sufficient and therefore see decide what is missing

Apply Normalisation Rules

You go through the process again and again until all the data you require is there and also in the correct table

Only when you are satisfied do you think about using Access

No I do not think a new table is required for each course

Year, date etc are an attribute of course or session, You decide based upon Entities

L

I certainly would not have a table per course or a table for a Year. Each course should have its own unique key which would be entered into a bookings table wich would contain your booking date.

Thank you for both of your commends. I will think again and share my idea.