Hi guys,

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

Don't put attendance in the events table, it's not part of the event. Create an events table, and an attendance table. The attendance table would have two columns: EventID and MemberID.

Again, with the Race information you don't want to create columns for each race (what if you add more races? You'll have to change your table to include more columns). Make a different table for Race. Use an intermediate table to link events/races and one to link races/members.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.