A County Club Data Model

Sometimes you need a break and you don’t want to travel all that far to get one. Why not go to a local or county club? We look at a data model to help county clubs everywhere stay organized.

Imagine that you’re in southeast England, somewhere in East Sussex or West Sussex or Kent. The grass is just the right length, the tea is the perfect temperature, and it’s raining a little. A usual day, but a little bit boring. What to do? Why not go to the local county club to meet your friends? Maybe there is an activity going on.

Today we’ll discuss a model that could be used to run a local county club or even a few of them. Grab your tea and let’s go to the club.

What Should Be Included in the Club Data Model?

Before I move on to the data model, I would like to discuss the “problem” we want to solve. I’ll do this with a series of questions and answers:

1. What is a local or county club?

   A county club doesn’t need to be in England and doesn’t need to include tea. We can expect some kind of local club in communities all around the world. Some of them will be related to sports or hobbies, while others could be related to culture, history, and folklore. All local clubs have one thing in common: they are related to a community and members of that community will organize and participate in club events.

2. Are clubs limited only to one type of event or activity?

   No. We can expect that these clubs will combine many different activities; after all, different members will have different interests. Some events could be very challenging to organize, e.g. a community Christmas Concert. Others, like a bridge tournament, could be very easy to organize.

3. So clubs have events. How complex is event organization? What will we need to store?

   I’ll call every action that involves some planning and organizing an event. The bridge tournament planned for next Sunday is an event. Somebody will have to open the club and get everything set up (tables, chairs, refreshments, extra tea…). We will need to know who will take part in the tournament, when it will start, and (roughly) when it will end. The event will also have some result, such as a tournament winner.

   Of course, organizing a Christmas Concert will be much more complex. There are many prerequisites: instruments, musicians, singers, other performers, sound equipment, chairs, tables, more refreshments, etc. And yes, there is a good chance something will go wrong along the way.

4. What ‘activities’ won’t be treated as events?

   The dividing line between events and other activities is simple: if advance planning is involved.

   We’ve already used the idea of a bridge tournament. That took a bit of planning. But suppose a couple of club members went to the club, saw there were no events for that day, and called some other bridge players for an afternoon of card playing. From the outside, this might look like a tournament, but it isn’t. We won’t call these events, and we won’t store them in the database. Although I suppose you could store them if you had some kind of secret bridge league or something. ??

5. What other data should we store in the database?

   We know we’ll need to store clubs, members, and events. But there is a lot more we need to organize. People who are part of a county club usually take it seriously. It’s something that improves community life by offering additional recreational activities. I guess life is all about playing, no matter if you’re 9 or 90.

   So, we need to be very detailed when we plan events. We’ll need to assign people to activities, define everything needed for the event, and possibly hire partners for whatever we can’t do ourselves. We’ll also need to define time frames for events and activities and track which location are involved.

6. Will this model be used for a single club or for multiple clubs?

   In most cases, we’ll have only one club per database instance. Having one database used by a few clubs could prove very helpful. We would have all the data in one place and we could easily track events taking place in other clubs. This would make it easier to collaborate with other nearby clubs, which is very important. Also, having more than one club in the same database would be very convenient when more than one club is organizing an event. Members of different clubs could be assigned to the same event and all the related data would be easily accessible for all clubs.

7. Last call.

   If you haven’t done it yet, make yourself a nice cup of tea, lean back in your chair, and prepare to enjoy the article.

The Data Model

The data model consists of four subject areas:

• Clubs
• Members
• Partners
• Events

We’ll present the subject areas in the same order they are listed.

Section 1: Clubs

The first subject area I’ll describe in this model is the Clubs area. It will store information for all the clubs that use this database.

The central table here is the club table. We’ll use it to store basic details, but we could include many more. Almost all of the fields (except the first two listed below and the primary key) could contain NULL values. These are the attributes we’ll store for each club:

• club_name – A UNIQUE name to denote that club inside our database.
• address – The address where the club headquarters is located.
• email, phone, mobile – Contact details for that club. We’ll store these values if we have them, but they are not mandatory.
• date_active_from and date_active_to – When the club was/is active. We’ll enter the value in the date_active_from if we know the date when the club was established. The date_active_to attribute will contain a value only if the club was closed.
• description – All other club details, in unstructured textual format.

We can expect that most clubs will have more than one section (i.e. more than one activity they provide for their members). For instance, a local club could have a chess section, a card games section, and a tennis and badminton section. We’ll need two tables to list all sections and assign sections to clubs.

The first table we’ll use is a simple dictionary of all possible section names (types). We can expect values like “sport – badminton”, “sport – tennis”, “chess”, and “card games” stored in the section_name attribute of the section dictionary. This attribute will contain only UNIQUE names.

The club_section table relates clubs and sections. For each record in this table, we’ll store:

• club_id – The ID of the related club.
• section_id – The ID of the related section.
• date_active_from – When this section was established. It’s not mandatory because we may not have this information.
• date_active_to – When this section was disbanded. This attribute will contain a value only when a section has been discontinued.
• description – Any additional information, in free textual format, related to this record.

I’ve intentionally decided not to have an alternate key in this table. Using the club_id – section_id pair as the UNIQUE key is NOT OK because we could disband a section and then reestablish it later. If that happened, we couldn’t insert a new record in the table. There is also a chance we could have two sections of the same type, e.g. a chess section for professionals and another for casual players. In that case, we could distinguish these sections using a different description attribute or by two different section_name attributes in the section catalog, e.g. “chess (amateur)” and “chess (professional)”. That is another reason not to use the combination club_id – section_id –date_active_from as the UNIQUE key.

If someone assigns a section to a club that already has the same section, I would opt to notify the application user and let them decide to proceed with the selected action or not.

Section 2: Members

Next, we need to describe club members and club roles. These roles will be used to assign members to club events. All tables needed to store these details are in the Members subject area.

For each member, we’ll store one record in the member table. I’ll go with only the most basic details here, so we’ll have only these attributes:

• first_name and last_name – The first and last names of the member.
• title – The member’s title. This could be “Ms”, “Mr”, “Dr”, “Lord”, etc.
• date_of_birth and date_of_death – When the member was born and when they died (if that happened).

Next, we’ll relate members with clubs and club sections. Note that a person could be a member of a club but not a member of a section, or that they could be a member of a section but not a club (e.g. a person plays golf at the club but does not have a club membership). If we only relate members with clubs or members with sections, we would lose information. So we’ll need two tables to store this information. These are the in_club table and the in_section table. They share almost the same structure, so I’ll describe them together. We’ll use the following attributes:

• club_id and club_section_id – Refers to the related record in the club or club_section table.
• member_id – Refers to the member table.
• active_from – When this membership started.
• active_to – When this membership ended, if it has ended. If the membership is currently active, this attribute will contain a NULL value.
• is_active – Is ”True” if the membership is still active and “False” if it is not.
• details – Any additional details related to that membership.

You will notice that we will use the attribute names active_from, active_to and is_active in a few more tables. They have the same logic, so it’s good practice to use the same names for them. This improves the overall readability of the model.

The role dictionary will store all possible member roles related to a club or club section. Each record will contain a UNIQUE role_name as well as two Boolean values denoting if that role can be assigned to club or section members. If both these values are “True”, then this role could be assigned to either club members or section members.

Now it’s time to assign roles to members. We’ll again have two tables with similar structure: the club_role table and the section_role table. For each record in these two tables, we’ll store:

• in_club_id and in_section_id – References pointing to membership in the club or section.
• active_from, active_to and is_active – The same as in the previous two tables.
• details – All additional details, stored in free textual format.

Section 3: Partners

If you want to organize a bridge tournament, you’ll need some decks of cards, tables, chairs, and players. We can expect that most club events will be organized by club members and use club equipment (e.g. cards, tables, chairs, tea supplies). But in some cases, e.g. the Christmas Concert, we’ll need to borrow equipment and hire professionals to help us. We’ll use tables from the “Partners” subject area for this.

The first thing we need a list of our partners. We’ll keep that list in the partner table. For each partner, we’ll store a partner_name, contact details (address, email, phone, mobile and contact_person), and any additional details.

All services provided by partners are stored in the service dictionary. Each service is UNIQUELY defined by its service_name. Aside from the primary key, that’s the only attribute we’ll store in this table.

Now we have to relate partners with services. To do this, we’ll insert a UNIQUE pair of partner_id – service_id in the provides_service table for each service that the partner offers. The details attribute is used to store additional descriptive information related to that pair.

Section 4: Events

The last subject area in our model is the Events area. This might seem really large, but it’s not. Six of these tables were already described in other subject areas, so we only have seven new tables here. This is where we’ll relate events with clubs, roles, partners, and members.

The central table in this subject area is the event table. This is where we’ll store records for all events and the clubs and members involved in them. For each event, we’ll need:

• event_name – A name we’ll use to UNIQUELY denote that event.
• start_time and end_time – When the event started and ended. If we don’t know when the event will end, this field can be NULL. We’ll update end_time when that information becomes available.
• location – A textual description of the location(s) where the event will take place.
• description – All additional details describing that event.
• for_club_members_only – If this event is available only to club members.

Next, we need to define leadership and roles for the event. To do that, I’ll use the club_in_charge table, the section_in_charge table and the event_role table.

The first two tables are used to define all clubs and sections that are in charge of the event. In most cases, an event will be organized by one club and/or one section, but all combinations are possible. The event_role table will relate section members with a specific role for that event.

All three tables share a similar structure, so I won’t describe each one separately. Each table contains the UNIQUE foreign key pair event_id – club_id/club_section_id/section_role_id and an additional textual attribute to describe details.

We’ve mentioned that we can expect some events that require partners. The next two tables are for those events.

We’ll store UNIQUE event_id – partner_id pairs in the event_partner table. The details attribute is used to describe the partner’s role in this event.

A list of the event services provided by partners is stored in the event_service table. It contains the following columns:

• event_partner_id – A reference to the partner’s engagement for that particular event.
• service_id – The ID of the service that partner provided. I’ve intentionally referenced the service table instead of the provides_service table. The reason is that the provides_service table doesn’t store a history, so we couldn’t delete a record from this table if a service has been previously used and that partner no longer provides that service.
• details – All additional details related to providing that service for that event.

I’ve used the event_partner_id – service_id pair as the UNIQUE value (alternate key) in this table.

The last table in our model is the visitor table. We can use it if we want to store records for all the visitors to club events. Maybe we need to know all the visitors in advance because there is a limited number of seats, or maybe we’re selling tickets for a certain event. Visitors could be club members, but that is not mandatory. Therefore I’ll “duplicate” the first and the last name if somebody is a member at the same time they are a visitor. For each visitor, we’ll store:

• event_id – The ID of the related event.
• member_id – The ID of the member. We’ll store this value only if the visitor is also a club member. Otherwise, we’ll store a NULL value here.
• first_name and last_name – The visitor’s first and last names.

What Do You Think About the Club Data Model?

Today we have discussed a data model we could use to organize events in our community. I tried to cover only the most important features so as not to write a book about this model. ☺ Therefore, some features (such as anything financial) are completely omitted. Do you think it would be wise to add that to the model? Would you add something else? Or change something? Please tell us in the comment section.