I'm a bit stumped at the moment trying to figure out a way to keep track of the following scenario -
I want to track a list of events and members of our club who attend them. Each member currently has a member ID as a primary key in the members table. They also have a record of times(for races) in a times table. Fees are kept in a fees table.
I'm thinking of creating an events table which has all of the user ids in one column and as events are created php will add a column with the event name to this table and populate a YES into the corresponding row for each member who attends the event. This will be used to pull out a list of who attended which event or the numbers at events etc.
I also want to create a table for each event using php which will share the name of the columns for that event in the "events" table mentioned above. Each event will have a column for each type of race that is being run. Each member who attends the event will be added(again via user id in the first column) and the time they run at the event will be populated in the corresponding column.
I'm new to the whole MySQL thing so I'm wondering A) Will this work? and B)Is there an easier way to achieve the same functionality?
I'm wary about implementing this as I'm wondering if I'll encounter problems selecting corresponding event tables from the name of the event in the column header in the "events" table I mentioned in the 3rd paragraph.
Any help, comments or insights are more than welcome