Wedding Organization Data Model

Emil Drkušić, Database designer and developer, financial analyst

by
Emil Drkušić
Database designer and developer, financial analyst

Posted: November 22, 2017

data model, model design, database design, wedding, management, hobby

Weddings are often accompanied by merriment and celebration, with numerous guests, food, drinks, music, and dancing. But all of this can’t happen without the proper preparation and coordination. Let’s take a closer look at how data modeling can help us better organize a wedding so everything runs smoothly.

Preliminary Background

Though we’re mostly all aware of what a typical wedding ceremonies looks like, it can’t hurt to briefly consider some aspects that could potentially impact our data model.

Wedding partners

Though most traditional cultures will have ceremonies between a man and woman, same-sex marriages also take place in other societies. Our data model should be designed in such a manner that it accommodates all possibilities.

Scale and complexity

Wedding ceremonies vary greatly in their size, duration, and complexity. Some are small, modest occasions, but others are grand celebrations. In Croatia, for instance, you can have a simple wedding ceremony where a couple get married in the town hall, exchange their rings and vows before their guests, and either attend an after-ceremony dinner or go home. In other countries, weddings can be quite elaborate: they may involve bachelor/bachelorette parties, negotiations, dinners, multiple ceremonies, and so on. In some cases, these ceremonies can last for multiple days and occur in a few different locations! Again, our data model should be prepared to handle these situations.

Final outcome and expenses

In most cases, the couple get married after the celebration and receive an invoice for all the costs (rent, food and beverages, band, etc). They may decide to hire an agency to take care of all these costs for them, or they may choose to handle it all on their own. Either way, we should account for these situations.

The Data Model: Overview




Our data model for this article consists of five sections:

  1. Locations
  2. Partners, Products, and Services
  3. Weddings
  4. Participants
  5. Invoices

We’ll thoroughly discuss each of these areas in the order they are listed above. As we work on developing our data model, we’ll assume the role of the agency organizing the wedding.

Section 1: Locations

Section 1: Locations

The Locations section features universal tables that can be used in many other data models. As we noted earlier, the entire wedding ceremony could occur in just a single location, or it could potentially span multiple locations. Let’s discuss the tables of this section in greater detail.

The country table stores information about the country in which the wedding takes place. In most cases, this country will match the location of our agency, but that may not be the case if we operate internationally. Each country in this table is uniquely defined by its country_name.

Next, we need to store the list of all towns and/or villages where the wedding will be organized. This information will be stored in the city table. For each city, we’ll store its name and postal code, as well as the country it’s located in.

The last table in this subject area is location. Locations are more specific, such as town halls, churches, parks, and so on. For each location, we’ll store its name and a reference to the ID of the city it’s located in. The combination of these two attributes forms the unique key for this table.

For locations, note that we’ve taken a conservative approach here to avoid covering the unusual cases in which the ceremony takes place in, say, a train or an airplane (in which case, the “location” may involve multiple cities). If we would like to cover these cases, we would need to make some changes to our model.

Section 2: Partners, Products, and Services

Section 2: Partners, Products, and Services

Before we move on to the central part of our data model, we need to store the list of all partners we work with, as well as the products and services they offer. To achieve this, we’ll use five tables.

First off, the list of all partners we work with is stored in the partner dictionary. For each partner, we’ll store their unique partner_code and partner_name.

Of course, our partners will provide wedding-related services, which could include catering, organizing bands, setting up audio and video equipment, providing rent support, and much more. Essentially, anything you can think of can potentially be related to a wedding in some manner. We’ll store this list of services in the service dictionary. For each service, we’ll store:

  • service_code – a value we’ll use internally to uniquely denote a particular service.
  • service_name – name of the service. Note that different services could share the same name. This would occur if two of our partners happen to offer the same service, which is quite likely. It would be even desirable if they use the same name for same service type because that would make comparing prices for same services much easier.
  • description – an optional textual description of the service.
  • picture – a link to the location where the associated service picture is stored.
  • price – the current price for this service. It can contain a value of NULL if the price cannot be determined without first evaluating various factors, such as how many people plan to attend the ceremony.

The provides_service table relates partners to the list of services they provide. For each unique combination of partner_id and service_id, we’ll store a detailed textual description of the nature of the service provided by the partner and whether the service is currently available.

We also need tables for storing information about products and their relations to partners. The product table follows the same logic as the service table, except, as the name suggests, it’s specific to products. In this table, we’ll store all possible products that are essential to most wedding ceremonies, such as rings, outfits, decorations, flowers, furniture, and more.

The last table in this section is the provides_product table. It works just like the provides_service table, except it’s specific to products as opposed to services. It specifies which of our partners offers the product in question.

Section 3: Weddings

Section 3: Weddings

We’ve finally arrived at the heart of our data model—the Weddings section. It contains five new tables that reference other sections’ tables. Note that this section’s own tables will also be referenced in upcoming parts of our model.

In the wedding table, we’ll store the complete list of all weddings we are/were involved in organizing. Each wedding will be assigned its own unique wedding_code. We’ll also store the planned start and end times for the entire ceremony, and we’ll update the real start and end times whenever this information becomes available. Additionally, we’ll store the budget_planned value so we at least have an estimate of how much this will all cost. All other details related to the wedding are stored in others areas of the data model, so this is all we really need for now.

The idea here is to treat each wedding as a series of events. Events in turn will be related to offers for desired products/services, rejected and accepted offers, and other relevant details. To give you a better idea of how this all works, we could split the entire wedding into the following events: planning phase, bachelor/bachelorette parties, ceremony, and after-party/dinner. Of course, these are just some of the most common wedding events. All wedding events are stored in the event table. An event will have a unique id.

Each event is associated with a single wedding, and it will either be related to one location or none. The latter case arises if the event is more conceptual, such as the planning phase (since there is no single location where it must take place). As with the actual wedding ceremony itself, an event will have planned and real start/end times, as well as a planned budget. Note that we’ve kept things simple here with regard to locations. If events involve multiple locations, we’ll need to adjust our data model.

Moving on, we want to store all services and products that are related to an event. To do it so, we’ll use three tables: status, product_included, and service_included.

The status table is a dictionary that keeps track of all statuses related to products and services for a particular event. It includes flag variables that denote whether a product/service has been offered, accepted, or rejected. For each record in this table, we’ll store a unique status_name.

The remaining two tables in this section, titled product_included and service_included, resemble each other structurally and conceptually. For each event, we’ll store the list of products and services that were offered and change their statuses if they get accepted or rejected. For each record in these two tables, we’ll store the following common attributes:

  • event_id – a reference to the related event.
  • provides_product_id / provides_service_id – references to the tables with products/services that our partners have on offer.
  • price – proposed price for the product/service. This price may differ from the standard price we have on file if we propose a special offer.
  • current_status_id – a reference to the status dictionary denoting whether this record was offered, accepted, or rejected.

Section 4: Participants

Section 4: Participants

If you’re organizing a big wedding, chances are you’re acquainted with most of the guests who plan to attend. Of course, the guests you invite—be they your friends or relatives—will likely bring other people you don’t personally know, such as their friends or colleagues. In this section, we’ll store the complete list of guests who have been invited to the wedding, as well as their roles.

The person table contains a list of all individuals who are part of the wedding. For each individual, we’ll store their unique person_code and first and last names. We can of course add more details if we’d like.

Next, we’ll define all possible roles that one could assume during a wedding. These roles include “guest”, “best man”, “groomsman”, “bridesmaid”, “bride”, “groom”, and so on. For each role, we’ll store only the unique role_name in this table. A person can only take on one role for a particular wedding.

Next, we’ll relate weddings to their participants. Notice that the participate table only contains references to the tables wedding, person, and role. The combination of wedding_id and person_id serves as the alternate key for this table.

The wedding will consist of several events, but not all participants will be involved in these. Therefore, we need to store this information separately. In the in_event table, we’ll store unique pairs of foreign keys referencing the tables event and participate. All additional information will be stored in the details text attributed.

Section 5: Invoices

Section 5: Invoices

We’re almost done! The last section of our data model allows us to track expenses related to the wedding. Exciting, right?

We’ll usually generate one invoice per wedding, but we could also generate more if we needed to. Hopefully, the total amount we invoice the couple will closely match our planned budget, but that may not always be the case. For each invoice, we’ll store the following information:

  • wedding_id – a reference to the wedding for which the invoice was issued.
  • time_created – the timestamp for when the invoice was generated.
  • due_date – the date by which the invoice must be paid.
  • invoice_amount – the total amount that must be paid.
  • payment_time – the timestamp of when the payment was actually issued. Of course, this attribute will contain a value of NULL until the payment is made.
  • paid – a flag denoting whether the invoice was paid. This attribute will be set to “True” as soon as the payment_time is updated.

The last table in our model concerns the invoiced items themselves. We’ll store these in the invoice_item table. For each record, we’ll store the following details:

  • item_name – our chosen name for the specific item.
  • item_price – the price that is related to that specific item.
  • invoice_id – the id of the related invoice.
  • service_included_id – the id of the service the invoice item is related to. This attribute could be set to NULL if the item in question is not actually related to any service or if it is merely an additional charge we’ve applied to the invoice.
  • product_included_id – the id of the product the invoice item is related to. This attribute could be set to NULL if the item in question is not actually related to any product or if it is merely an additional charge we’ve applied to the invoice.

Summary

That pretty much sums it up for this data model! Once again, we see just how useful data modeling in organizing a company’s information.

As we noted, there are many things that we omitted from our data model for the sake of simplicity. For instance, our model should ideally track offer histories, financial details, and more.

Let us know down below if you have any suggestions. We’d love to hear your thoughts!

 
 

Try our online database modeler. No registration. No commitments.

 
 
Tags
 
Subscribe to our newsletter

If you find this article useful, join our weekly newsletter to be notified about the latest posts.

 
 
 
Over March 14–21, grab all Vertabelo Academy eLearning 78 percent off.