I’m a newbie here and it looks as if I might be able to get a bit of advice on what I think should be a fairly simple project that would actually have some value in my real world if I managed to get it working.
I have successfully set up a web site with a MySQL database and I can connect to it using some simple PHP code, so that part is out of the way. The challenge will be to build on it.
Here is the background. I would like to build a database to record attendance at a variety of activities that are organized into categories. The first name and last name of the person attending, the activity category such as Outdoor Sports, Cultural or Intellectual and the actual activity is really all that is required.
Examples of activities by category might be as follows:
Category = Outdoor Sports (Activities = Archery, Swimming, Tennis)
Category = Cultural (Art, Music, Drama)
Category = Intellectual (Lectures, Seminars, Discussion Groups)
The categories are fairly static, however the actual activities are somewhat dynamic in that new ones are regularly introduced and some are dropped from the program.
Reports that would be desired might be as follows:
• All activities attended by a person between two dates, or for the current or last month, etc.
• Persons who attended a particular activity.
• Persons who attended activities in a particular category.
• Count of attendance for a particular activity between two dates.
• Count of attendance for a particular category between two dates.
• And probably a whole lot of goodies that I haven’t thought about yet.
Some database maintenance requirements would be:
• Add, delete or modify a person (First name, Last Name)
• Add, delete or modify an activity, and possibly a category.
For the record, there could be a few hundred people in the program, possibly about 10 categories of activities, and approximately 50 unique activities. Occasionally an activity might be scheduled more than once on a particular day, so I will need to think about how to deal with that situation.
Currently entering the data is a tedious task, and I am thinking that I would like to set up a data entry form where the user could use drop down list boxes to select the person who attended with a mouse click, another drop down box for the category that would then offer a list of specific activities. It would be handy if the date that the activity could be entered by means of a calendar. I actually built something like this using VBA in Excel, so it will be an interesting challenge for me to see if I can make it happen in PHP.
So to the question on the design.
I’m thinking that I should have a “person” table that contains the first and last name of the person and a master_id field. Then a table that has the date and a master_id field that would be the same as and linked to the master_id in the person table. Then the same idea with the category table that would have a master_id field linking back to the “person” table’s master_id. And the same design for the activity table.
Table = person
Fields = id (index), master_id. first_name, last_name
Table = date_time
Fields – id (index), master_id, activity_date, activity_time
Table = category
Fields = id (index), master_id, activity_category
Table = activity
Fields = id (index), master_id, activity
I would like to have some confidence that I am on reasonably solid ground with this database design before I get to far along with this. This is the only design that I can come up with that I think will satisfy the reports that I would like to extract from the database. However I know very little about this, hence my pleas for some help.
Any advice or suggestions would be mighty welcome, and if you could point me to some PHP code and a MySQL database that might behave somewhat like I am after, it always helps my learning process.
I hope that this all makes sense.
Thanks for the time that you are taking to help me.