I am in the process of working on two information systems. (1) a bar and nightlife-related website (using the pseudonym “Funsite”), and (2) a membership database for the St. Louis Chapter of the National Black MBA Association (NBMBAA). As my understanding of the business requirements for each organization has deepened, I have had to redesign the respective conceptual data models for each system.
The original model for FunSite included a Bar table (with information such as bar name and bar address) and an Event table (with information such as event date and event time). When an event instance was created, it would be associated with a bar via a foreign key linking to the bar’s primary key (unique identifier). This depicted a one-to-many relationship, with Bar as the parent table and Event as the child table. However, after talking with colleagues, we realized that certain events may have multiple bars participating in the event (for example, pub crawls or West Port Plaza’s “Party on the Plaza”). Unfortunately, the current model did not allow for these “multi-bar” events. Since this now depicted a many-to-many relationship, the proposed modification was to create an associative table named Bar_Event which would require the primary keys from both the Bar and Event tables.
The original model also called for the Event table to contain all information related to the event. After creating the associative table Bar_Event, we realized that while multiple bars may participate in a single event, they may each have unique characteristics, such as different start times or cover charges. This led to the transfer many of those attributes from the Event table to the Bar_Event table. The Event table now only contains high-level information about each event instance (event_id, event_name), allowing flexibility for bars which require processing of complex events. Regarding the Conflicting Objectives criterion listed by Simsion, we understand this new model will result in increased storage and processing requirements. As of now, we are willing to make that trade-off for the sake of flexibility, but the possibility exists that we may find that storage and processing requirements are too onerous for the design.
National Black MBA Association (NBMBAA)
I began working on a membership database after my first interview with the Treasurer of the NBMBAA’s St. Louis Chapter. At first glance, the system requirements appeared straightforward; however, after I submitted my initial conceptual model, I learned of a few additional requirements:
- Members Can Pay Dues in Partial Payments – Initially, I designed the model to only accept lump sum payments using a Member table as the parent and a Member_Dues table as the child. After learning of the partial payment option, I incorporated an additional table named Member_Dues_History which offers the option of recording multiple member payments on a single membership balance. This new table created an additional one-to-many relationship between the Member_Dues (parent) and Member_Dues_History (child) tables, as shown below :
- Sponsoring Corporations May Opt Out of Events – The NBMBAA works with local corporations to promote higher education. Sometimes this includes hosting various events such as networking sessions and golf outings. During my third interview with the NBMBAA treasurer, I learned that some corporations in the past have expressed a desire to opt out of certain events (such as golf outings). Given this information, I created an Opt_Out_Event table which links the unique identifiers corp_id and event_id from the Corporation and Event tables, respectively. The corporations who comprise instances in this table will not receive any literature or any other communication regarding the associated event.
- Street Addresses Are Not Required – In subsequent interviews I also learned that since most communication between the local NBMBAA chapter and members is electronic, the street address attribute in the Member table should not be required.
According to Jonathan Geiger, executive Vice President at Intelligent Solutions, Inc., “The data modeler needs to comprehend the new requirements [of the proposed system], determine how to incorporate them into the existing data model, and most importantly, understand any downstream implications of model modifications” . After my experiences with the two systems listed above, I fully appreciate Geiger’s declaration. I hope to hone my interviewing acumen so I can ask the right questions in order to facilitate my understanding of organizational business requirements. By improving in this area, I believe I can minimize the amount of critical design changes between iterations of my conceptual data models.
The next section will conclude this paper with a few closing thoughts.