Too complex for this run at the system. Add in the date-based prices and what do you have? A mess. Automation of the date pricing is a little involved. What if someone pays just before the early-payment date (there is typically a rush of payments that day) and you enter their payment the next date? System says they’ve passed the date and must pay more. Automation is not always your friend.
You will be doing it, because it will be manual not automated.
I’m happy to implement moderately complex structures now, because they don’t add a lot of time to development. But complex business rules will come later.
I thought it was a bit too complex for practicality, which I why I didn’t suggest it earlier. Dates would only be an issue if the price fell over time. This system would be for advance bookers/payers, so people paying on the day would not need to be entered… Still, it would be good enough to at least have the price structure in a text field for people to work out themselves what they should pay.
The tristate flags will be handy so that we only have to verify discount status once - but should there be an expiry date to go with the flags?
Why? I imagine that we’d enter all payments including those at or after the game, so that the organisers of the game have a record of who was at the event and who paid. Useful for chasing up people who did pay and that sort of thing.
Besides, it’s not just special gate prices that are a problem for an automated system. Mordavia’s early payment discount would be hard to implement automatically, which is the example I was using.
Just to be clear, I see this as the central and main respository of participation data for LARPS projects. Project organisers don’t just need a place to centralise information before events, they need it for later reference too.
LARPS membership expiry? Probably a good idea. I think the “user” table as being the central respository for member (and non-member participant) information for LARPS.
Rhiannon, I know you currently have some sort of file or database for storing the membership info in. I think this would be more accessible as a central online database, viewable and updatable by whichever committee members need it. What data about members are you storing? We should include the same fields in the “user” table.
What about student expiry? I guess so. Who exactly will be able to validate whether people are students? Any event organiser? That could be open to abuse. Should it be a LARPS function?
Okay, I’ve added the additional fields to the user table that I think are being stored - cell phone, occupation, etc. I’ve used “birth date” instead of an age because ages change, and birth dates don’t. I’ve also added a minimum age option to events so that organisers can get a warning if someone under age signs up (they could then let them sign up at their discretion, it will just be a warning message). This will also be useful for any events that use age-based pricing (although - again - I won’t be automating pricing).
TABLE: membership_status
This lookup table will contain several membership states, such as “Not a member”, “Applied for membership”, “Verified member”, and “Expired member”. It can be used both for society membership stats and validated student membership status. Validation will be done by a society committee member. The “verified_member” flag field will be useful for future automation (the name might change so hardcoding against it would be unwise).
membership_id - int - PRIMARY KEY name - varchar(50) verified_member - bit display_order - int
I am not suggesting the implementation of a complex rules engine to handle the myriad pricing options. As you suggest, the actual business rules would be implemented manual by the organisers.
However, I think there should be a field to record the total due simply to assist the event organisers to validate how much people owe. Since amount_paid and amount_outstanding will typically be edited in the same transaction, surely it reduces the risk of inaccuracies if their is a third, generally immutable field that records the total due to assist in validation ?
I agree that total_due would not be entered until such time as the first payment is received. If a critical event occurs that may affect a total_due amount, then the organisers will need to review/update the affected participants.
For example, if a participant decides to join NZLARPS, the organisers would need to update the total_due and amount_outstanding fields for the participant if they have already paid a deposit for the event.
Likewise if a discount date passes, the organisers would need to review all participants who have not paid in full and increase their total_due field and amount_outstanding fields.
The complexity I was talking about was not in the SQL, but in the consequent result set. If you wanted to return Project Name and Event Name in the same result set, one would have to be aliased. At which point you would have result set field names that do not match the underlying table field names = increased complexity for no gain.
But then again, maybe you weren’t going to work with more than one table at a time.
