help in designing a database (1 Viewer)

[graemespence1]

Hi i need help in the basic design of a database which I will outline above
The database is to record user numbers for activities that the centre runs and i need to record user numbers not only for activites that run once a month but also weekly. The main problem is that I need to be able to add and remove activities at the monute I have a monthly activities table which records the month and year and each of the monthly activities and a weekly table linked to the monthly table by the Month/year field

Month
(Primary Key) Month/year (Date field)
Activity 1 (Number)
Activity 2 (Number)

Week
(Primary key) Week commencing (Date field)
(Foreign Key) Month/year (Date Field)
Activity 1 (Number)
Activity 2 (Number)

The problem with my current design is that if i want to add or remove a activity i not only need to change the table fields but also redesign the forms and also the queries which I use to generate the reports and this adds a lot of time as the queries I use are linked to other queries I have to often edit a lot of queries and check that everything works afterwards.
What i'm wanting is any ideas to see if I can have it so that activities can be easily edited and added and removed.
Any help would be grateful and if you need extra info I will be happy to provide more.

 

[Isskint]

graemespence welcome.

The approach i would take is to record each activity as a seperate record, include a field to show what frequency the activity is and a different PK. How about;

ActivityID - AutoNumber (PK)
ActivityOccurance - Number
ActivityName - Text
ActivityFrequency - Text

Then a seperate table to store the calendar of events;

EventID - AutoNumber (PK)
ActivityName - Number (FK - ActivityID)
EventDate - Date
NumberOfUsers - Number

If you want to record user details and not just how many took part then you would want ;

UserEventID - AutoNumber (PK)
EventID - Number (FK - EventID)
UserID - Number (from a seperate table of users details)
OR
UserName - Text