Website database

nzLARPS
#1

Mordavia has an immediate need for an online system where the organisers can share information about who is playing at our events and who has paid.

I’m going to write a system to handle this, and make it generic so that it can be used by other NZLARPS projects too. I picture that it will eventually be located on the www.nzlarps.org website and form a part of the integrated database on there.

So for those other programmers out there, that means it will need to be not only generic, but also extensible. See the LARPS website content topic for discussion of the functionality that might be wanted on the NZLARPS website.

I’m going to write up a spec for the database tables here, for those with as technical bent to critique. Please do make suggestions for improvements.

It’ll be a MySQL database with a PHP script frontend.

TABLE: project

This table will contain projects run within NZLARPS. For example, Mordavia and Executive Decision will be projects. A project can have a number of events, that will be stored in the “Event” table. For example, Mordavia: Solstice would be stored there. In some cases a project will only have one event (e.g. possibly the Epic Greek larp), however it will still have one project record with the event record under it.

Data from each project record will eventually be displayed on its own page, as a public description of what that project is about. At that point the project may want to have it’s own look and feel, which is why the project has a link to it’s own PHP template page. If blank a default will be used. The template will include things like colours, layout, fonts, and logos.

project_id - int - PRIMARY KEY
owner_id - int - foreign key to user table
treasurer_id - int - foreign key to user table
name - varchar(100)
short_description - varchar(1000)
long_description - text
closed - int
template - varchar(100)
approved - bit

TABLE: event

This table will contain events run under a project. For example, Mordavia: Solstice would be stored here.

Data from each Event record will eventually be displayed on its own page, as a public description of the details of that event. Look and feel will be inherited from the

event_id - int - PRIMARY KEY
project_id - int - foreign key to project table
organiser_id - int - foreign key to user table
treasurer_id - int - foreign key to user table
name - varchar(100)
short_description - varchar(1000)
long_description - text
cancel_decision_time -varchar(100)
cancelled - int
publicise_participant_statistics - int
template - varchar(100)
image_url - varchar(100)
start_time - varchar(100)
end_time - varchar(100)
venue - varchar(100)
directions - text
cost - varchar(255)
minimum_age_in_years - int
what_to_bring - text
additional_image_url - varchar(100) - may be used for maps showing directions / picture of organisers fs
additional_description - text - may be used for additional text after block of event details

Okay, and here’s where I get to a sticky point. We’ve got projects, and we’ve got events run in the projects. However, what I really need at this stage isn’t all that stuff (that’s for future use), it’s a way of making a list of participants at events for use by the organisers.

The sticky bit is, how should the participant data be entered. I see two options, although there may be others:

  1. Create a “participant” table. Link every participant to one event, include all the data about their relation to that event (what they’re playing, we’re they have paid) in that one record and get the organisers to manually enter all the details for all the participants for each event. This is very simple to implement. However, over time there will be an awful lot of data reproduction, as the same participants get entered into multiple events within a project, not to mention events in different projects.

  2. Wouldn’t it be nicer if the organisers could just select from a list of existing participants in the database, and only add new ones when they’re really new? In this design there’s a “user” table (something the system will need anyway for the organisers), and all participants in events get entered into it. It would have a many-to-many relationship with the “event” table via a “participation” table that lists the details of the user’s participation in the event (what they’ve paid, etc). This is really generic and adaptable, and minises repeated data. The tricky aspect of this is that sometimes users would need to get created by event organisers, rather than the user themself. And if the user later wants to create an account to do something on the site (like run their own events, or have a “home page”) things get messy because a user with their name exists, and how do they take control of it?

Thoughts appreciated.

Event bookings
#2

[quote]The tricky aspect of this is that sometimes users would need to get created by event organisers, rather than the user themself. And if the user later wants to create an account to do something on the site (like run their own events, or have a “home page”) things get messy because a user with their name exists, and how do they take control of it? [/quote]As part of the signup process, ask if the person has an existing user id. If they don’t, the organiser can create it and when they confirm the signup also send the new account name and default password.

#3

I getcha. When someone signs up to attend an event by emailing or phoning the organisers, they get asked “are you registered on nzlarps.org?”. At that point they could register themselves and sign up for the event on the site, or the organiser could offer to register them and sign them up, then send them their registration details.

I think that implies that most participants will register on nzlarps.org and sign up to events themselves, which seems logical and takes some more strain off organisers. But it means we’d need public access than I was originally intending to design. Need a self-registration page, a change-my-password page, a list of projects and events under them so that the users can find the event they want to sign up to, and a page for signing up to the event.

I think you’re on to the right model though. It would make things a lot easier for organisers, and participants would only need to sign up once and would have a record of their sign-up status that they can go back to.

I think that requires a “user” table and a “participant” table. We might also want an “event_role” table. Will start drawing those up.

Any comments on the structures of the tables so far?

#4

I’d add a Title field and split Description into ShortDescription and LongDescription for both projects and events.

That way you can have:
Mordavia | Dark Fairytale | My really neat game wot I set in 1463 kinda near Transylvania yadda yadda yadda
Gods of Olympus | Epic Greek Larp | It’s going to be So Cool. We’re heading out to this island and we’re all going to dress up in himations which look like togas but aren’t. *

What I mean is, from some views, like a list of all events, what you really want to see is a name and a quite short description, but if you’re interested and would like to find out more, it’s helpful to have a longer description available when you select the event page.

  • Please forgive the made up names.
#5

This is a really good idea, and something we definitely need. The table should be editable by the organisers so as to add details like who is joining, who has paid, and how much.

In some cases, you may want it to be world-readble, so people can see at least how many confirmed participants (IE, paid) there are and how many unconfirmed (registered but not yet paid)

Would you be able to let anyone add themselves to the list (with a paid=N flag of course)? This might be useful in some cases, since people could book themselves onto an event, to be confirmed when paid.

Although it takes more storage space, it may be easier for names to be just a text field rather than held in another table. However, maybe it could link in to the phpBB authentication and usernames, making things simpler…

#6

[quote=“Steve Shipway”]This is a really good idea, and something we definitely need. The table should be editable by the organisers so as to add details like who is joining, who has paid, and how much.

In some cases, you may want it to be world-readble, so people can see at least how many confirmed participants (IE, paid) there are and how many unconfirmed (registered but not yet paid)[/quote]

Yes. I’m thinking that this may be event-specific and possibly time-specific. For example, once the organiser has lots of people signed up they want to show off their numbers online (against the event record) to get even more people interested. I’ve added a “publicise_participation_statistics” flag to the event table to allow this. The organiser can turn this on or off whenever they like.

This is what Steph’s suggestion implied, and the way I’m heading. It makes the most sense if people can sign themselves up to events.

I think we’ll go for another table, because we want self-registration to be the main approach.

I don’t want to link to phpBB this time, it’s restrictive and I’m not sure if there will even be a forum on the nzlarps.org site. I’ll build a sign-in page.

I’ve also added a “approved” flag to the project table. People with project ideas can enter them into the system whenever, but the committee must approve each project before it becomes visible to the public.

#7

Steph - I’ve split the description field into short and long, good idea.

The “name” field is intended to be the title. Do you think it needs both a name and a title?

#8

[quote=“Ryan Paddy”]The “name” field is intended to be the title. Do you think it needs both a name and a title?[/quote]No, I just hadn’t spotted the “name” field when I looked at it before.

Cheers,

Steph

#9

TABLE: user

This table will contain all the registered users on nzlarps.org. These are not necessarily members of NZLARPS, as it’s possible to play in NZLARPS events without joining.

Users will eventually have their own profile pages where they can talk about themselves, so fields for that are included here.

user_id - int - PRIMARY KEY
society_member_status - int -foreign key to membership_status table
student_status - int - foreign key to membership_status table
society_member_expiry_date - date
student_expiry_date - date
email_address - varchar(100)
first_names - varchar(50)
last_name - varchar(50)
nickname - varchar(50)
short_description - varchar(1000)
long_description - text
image_url - varchar(100)
template - varchar(100)
home_phone_number - varchar(100)
work_phone_number - varchar(100)
mobile_phone_number - varchar(100)
address_1 - varchar(100)
address_2 - varchar(100)
address_3 - varchar(100)
address_4 - varchar(100)
occupation - varchar(100)
birth_date - date

TABLE: participant

This table links events to users, indicating which users have signed up for which events. These records include details of whether the participant has paid for the event and whether they’ve been verified, and faciliates messages between the user and event organiser about the sign-up. Users can self-register for events or be added by organisers.

user_id - int - PRIMARY KEY
event_id - int - PRIMARY KEY
event_role_id - int - foreign key to event_role table
user_message - text
organiser_message - text
organiser_description - text
amount_paid - decimal(10,2)
amount_outstanding - decimal(10,2)
verified_participant - int

TABLE: event_role

This table will contain roles that participants can take on at events. Each event has a set of roles listed against it, and will usually be things like “Player”, “Crew”, “Cook”, “Organiser”, etc.

The event organiser creates the roles for their event and determines whether participants can select those roles themselves or not. Some roles can only be assigned by the organiser, and have special permissions to view the event participation data.

event_role_id - int - PRIMARY KEY
event_id - int - foreign key to event table
name - varchar(100)
self_selectable - int
view_participant_statistics - int
view_participant_details - int
edit_participant_details - int

#10

Okay… those are the tables that I think will be needed for this first phase.

Let me know if you see changes or additions needed in those tables, or other tables that will be needed for this.

#11

Shouldn’t this be integer and a secondary key field?
If you want to be classy, you could maybe add an event_role_type field with some standard entries like Player, Crew, Cook etc to limit the amount of typing that has to be done at setup. Although that’s a degree of data normalisation that might end up more trouble than it’s worth.

Steph

#12

Shouldn’t this be integer and a secondary key field?[/quote]

Ja, fraulein, es ist gut.

I think that might be over-normalised. There’s bound to be some role types that we don’t enter, and then the organiser would have to have an interface to add that new type, and every other organiser would get it on their list to choose from, and so forth. Like you say, possibly more trouble than it’s worth. That would also result in more complex join statements with four tables joined linearly (I’m sure there’s a better word for it, but you know what I mean) just to show event participation stats. Seems like overkill. Might put some hardcoded shortcut for adding “Player” and “Crew” in the interface.

#13

[quote=“Ryan Paddy”]Seems like overkill. Might put some hardcoded shortcut for adding “Player” and “Crew” in the interface.[/quote]Maybe just make a table of commonly used terms as a default that supplies a combo box on the setup screen. I know it’s possible in Access, which means that it ought to be possible in other more sophisticated DBMSs. Or alternatively, not bother about it. :wink:

(Slow day at work, hence critiquing someone else’s project is more interesting than working on my own.)

Stephanie

#14

Looks good to me from here.
No suggestions.
If you want any testing done once set up let me know.

#15

Cheers for the ideas and offers.

I’ll probably start implementing this on Monday. Craig’s been good enough to throw together a pre-live copy of the NZLARPS site for me to play on.

Any further design suggestions welcomed.

#16

Another thought, this one longer-term.

One of the things that project organisers need to be able to do is keep their potential participants updated with news about the project. Mostly about events, but also about things like changes in the rules.

What if the users could “subscribe” to as many projects as they like? Then the website could have a Send News page for project organisers, who could type up an email that would get automatically sent out to all the users subscribed to that project. The project owner could also view the subscribed list to get an idea of how many people in theory are interested in the project, and who they are.

Would require a “subscription” table in the database, creating a many-to-many link between users and projects.

On another line, I was thinking that things like the Community Officers’s promotional events could be stored in the system too. The officer could have an ongoing “NZLARPS Community” project, and add events to it as they arise. Workshops, parties, promotional stunts, whatever.

#17
#18

Looks good to me. Some suggestions / comments:

  1. Consider using unique field names. I have found that having duplicate field names leads to unnecessary use of aliases in queries where both tables are present i.e. increases complexity for no gain. So, in the Project table, long_description would become project_long_description etc

  2. It may be useful to have a field in the Event table to indicate at what time the go/no go call will be made if the weather appears to be inclement. This would be useful for participants because they would be able to plan around when they would know for sure if an event is being cancelled.

  3. Add a total_due decimal(10,2) field to the Participants table. Given range of prices available for a single event (due to discounts, different participation roles etc), it would be advisable to have a single field to store the total due. If payment is made piecemeal (e.g. a deposit first followed by full payment) then the amount_paid and amount_outstanding fields will be edited a couple of times during the life of an event for that participant. Without a concrete total due field, I foresee organisers making mistakes when adjusting the paid and outstanding figures. You could also have a trigger or other function that confirms amount_paid + amount_outstanding = total_due

  4. Given that the site will track how much people owe to the organisers, I would suggest adding an audit trail table to assist in tracking down any errors. It would track important events, particularly those to do with updating who owes what.

TABLE: audit

This table will contain audit data about specific activities. Each record will contain the name of the activity that was being undertaken (supplied by the web page that the record was being edited by). The action_value field will contain the names of the edited fields, and old and new values. This could be stored as XML. Not all edits will be subject to this auditing, only ones deemed important.

audit_id - int - PRIMARY KEY
user_id - int - secondary key (link to user)
action_name - varchar(50)
action_value - text
action_time - datetime (defaults to current system time)

BTW, I have never come across “secondary key” before. I assume it means foreign key. Maybe it’s mySQL thing ?

#19

Since this thing is getting larger and larger, heres something I held off mentioning.

The player database should also have a ‘student’ tri-state flag. This would be no/notconfirmed/confirmed. When signing up, you say you are a student (not confirmed) and it becomes confirmed when an organiser eyeballs your card and updates the database. Similarly, the NZLARPS-member flag should be tristate like this.

Then, in the event tables, the cost should be in a separate table with multiple entries - for NZLarps member, student, etc. so that different prices can be shown and automatically selected. A ‘not confirmed’ flag would be treated as a ‘member’ flag in this case, I guess. You’d need things to have defaults for prices - such as if i just specify a single flat rate. Maybe multiple playing options (like in Mordavia where you can be player or crew). Oh, the amount of work involved could be huge. Glad I’m notdoing it…

#20

Definitely a style thing. Personally I prefer to alias and have something like p.long_description. I find an alias like “p” for project or “u” for user more readable than constant repetition of the table names through the queries.

A cancel_decision_time field? Sure. For the moment I’m making these date fields text data types so that the event organisers can add something descriptive. Works so long as we don’t want to run calculations off them.

But then… we won’t be able to tell which events are past, or past their cancel time. Maybe they should be date types.

This is nice in theory, but an arse in practice. How much a given participant owes in total is kind of an unknown (or rather, it may change over time) , because many events have time-based price brackets (early price, gate price, etc). I imagined that both the amount_paid + amount_outstanding would be blank until someone paid, then the Treasurer would enter them both based on their decision of what is owed at that time. The alternative is to code for every pricing system imaginable.

I think I’ll do it the simple way for now and get it going. More complex requirements canbe hashed out and added later.

Okay, I can see the value in that and it should be simple to implement.

I meant foreign key. Secondary key is an indexing term. My bad.