Reporting on an Event spanning multiple days

[dansmith65]

I am working on an Event management database. Each event can span multiple days. I need to keep track of people who attend the event and how much they get paid for attending the event. Each person can attend any number of days of the event, although it is most common for people to attend the entire event. An example of the scenarios I need to handle: if a particular event lasts 5 days, Adam will attend all days of the event, Benjamin will attend the first 3 days, and Caleb will attend the first and last day. From this data, I need to be able to create a report with totals for each day of the event (who attended the event that day, and how much everyone was paid on that day of the event).

One way I am thinking of doing this will involve these tables: Contacts, Events, EventDays, Attendance.

For each day of the event, a related record in the EventDays table would need to be created. Then, for each contact that attends an event on a particular day, a related record in the Attendance table would need to be created.

The down-side of this method is data entry. (considering it is most common for a person to attend the entire event) In the example above, Adam is attending every day of the 5 day event, so there will need to be 5 records in the Attendance table for Adam, for this event.

My problem is that I can't think of an alternative method that would provide a straight-forward data entry method, and still provide the ability to create the daily report I need.

[Fitch]

I think your attendance table concept is sound. That will make reporting a piece of cake. You can create your interface in various ways to ease the data entry, for example an "all days" button that creates 5 records in the attendance table.

[dansmith65]

That's what I was thinking of doing; using lots of scripting to perform redundant data-entry tasks.

It felt a little cumbersome having to create so many related records, but it was the only way I could think to do it provide the reporting capabilities I need.

[comment]

There is nothing redundant or cumbersome here, except maybe the EventDays table - I don't quite see the need for it.

[dansmith65]

comment, can you elaborate a little on how you see it working without the EventDays table? That was one of the aspects that felt cumbersome to me; I was going to have to create a related record for every day in the range of the EventStartDate to EventEndDate, just so that Attendance records could be related to them.

[comment]

Can you elaborate on why do you think you need this table? It doesn't contain any information that isn't already known from EventStartDate and EventEndDate.

[dansmith65]

I've given it some thought and only come up with two reasons:

1) I also need to report on another entity which I hadn't previously mentioned because it has the same relationship to an Event as does Contacts and Attendance tables. The only way I can think of to show a daily report on both Contacts Attending the event, and this other entity is through the EventDays table.

2) It reduces redundant data storage. The alternative, as I see it, is to move the date field to the Attendance table. If there were 60 people attending a particular day of an event, then that date is stored 60 times, rather than once in the related EventDays table.

[comment]

Well, yes - the Attendance table should have a date field. A date is a unique identifier and storing it 60 times is no more redundant than storing an EventDaysID 60 times. The only valid reason (in terms of data modeling) to have an EventDays table is if you have something to say about a specific day of a specific event - something that describes the entire day, but not the entire event.

Re #1: if you want to report on two child tables in the same report, you will run into the same problem - with or without an EventDays table.

[dansmith65]

Thanks for the help comment, I needed a little push to think this through a little further.

Well, yes - the Attendance table should have a date field. A date is a unique identifier and storing it 60 times is no more redundant than storing an EventDaysID 60 times.

The only difference being if an event was moved forward a day, and the Attendance of that event should also be moved forward one day, using an EventDays table provides a single place to change the date. In this scenario, Attendance would not have a date field, only a foreignKey_EventDays field. In this scenario, one could argue that the Attendance should be changed one at a time, as you confirm with people who are attending. That is where the intended use of this database comes in; that's not the case, it's more of a "I'm going to tell Adam to be at X Event on Y days".

The only valid reason (in terms of data modeling) to have an EventDays table is if you have something to say about a specific day of a specific event - something that describes the entire day, but not the entire event.

That is very well put, I hadn't been thinking of it in those terms. Yet, I still disagree, as it applies to the system I'm designing.

Re #1: if you want to report on two child tables in the same report, you will run into the same problem - with or without an EventDays table.

I don't agree on this. Since the report needs to show totals per day, I could base the report on the EventDays table, and include a summary field from the Attendance table, and a summary field from the Other table I mentioned.

[comment]

The only difference being if an event was moved forward a day, and the Attendance of that event should also be moved forward one day.

That would only make sense if you are talking about registrations, not attendance. In such case people would register for say, days 1, 3 and 5 of a 5-days event, and the actual dates would be calculated.

Since the report needs to show totals per day, I could base the report on the EventDays table, and include a summary field from the Attendance table, and a summary field from the Other table I mentioned.

Well, yes: if you want the report to show summaries only, such table would be convenient (though it could be done in other ways). But in such case - assuming you will be reporting on one event at a time - the table needs only a few permanent records (the maximum expected days of any event) and again, the actual dates would be calculated from the selected event.

[dansmith65]

I think at this point I'm going to agree to disagree. I do understand the points you are making, and they were methods that I hadn't originally thought of, which was one of my goals of posting this; to find alternative methods of doing the same thing. I am beginning to see that part of the disagreement stems from me not divulging the exact use-case scenario (for my clients privacy), and using inaccurate terminology (Attendance probably should have been referred to as Registrations or Assignments).

Regarding the report, I am mostly interested in summaries, and I do need to report on more than one event at a time. I will probably also have a portal to the Attendance table on a layout based on the EventDays table, to show the detail of Attendance records for a single day of the event.

My other goal for posting this was to be clear on how I am going to set this system up, which you have also helped me with. So, thank you for your time and expertise.

[comment]

me not divulging the exact use-case scenario

That will get you every time.

I do need to report on more than one event at a time.

I don't see that makes much of a difference - other than needing more records in the reporting table.

I will probably also have a portal to the Attendance table on a layout based on the EventDays table

That's not a good idea, IMHO - esp. with 60 people attending on a single day.

[dansmith65]

That's not a good idea, IMHO - esp. with 60 people attending on a single day.

Not a good idea because of slow performance?

[comment]

No, because portals do not print well across page breaks.

[dansmith65]

The portals will be for the user to view/edit fields in the Attendance table only; they would not be for printing.

[comment]

I am not really into discussing a theoretical scenario, but a portal showing Attendance records for a specific date doesn't require an EventDays table. All you need is a global field to hold the specific date.

[Ertjie]

How about creating a value list with the different days. Then create a portal in your contacts table layout that points to the attendance table. Set the table relationship to allow creation of records via the relationship. The portal then only needs to have one field - Day. Example Day 1, Day 2 etc. Then each time the person comes in you just go to his user record then select a new day from the portal (on a new portal row each time). This will automatically create a new record in the attendance table and set the primary keys so its related to the record that it was created from (in this case the user). You can also do it vice versa, create a value list with user names and just add the users to the specific day. If users are going to be coming and leaving on variable basis, ie without you priorly knowing who will attend on which days, I think something along these lines might be best. Just an idea!