FOXCORE RETAIL (A): DESIGNING A DATABASE On April 2, 2017, Liam Corrigan and Mitchell Fox were wrapping up a weekend-lon

[answerhappygod]

FOXCORE RETAIL (A): DESIGNING A DATABASE
On April 2, 2017, Liam Corrigan and Mitchell Fox were wrappingup a weekend-long meeting discussingtheir upcoming summer plans. The two business partners werepreparing for a third year managing theirsmall retail business and had spent the weekend reviewingbudgeting, staffing schedules, and inventoryplanning for their biggest expansion to date. There was only onepressing concern that had not beensolved. In the past, employees had tracked sales by hand, on paper,which led to headaches when it cameto calculating sales representatives’ commissions. Additionally,the two partners knew that by notdocumenting key information about each sale, they were missing theopportunity to collect valuable datathat could provide insight into the performance of employees,events, and products. As the business grew,Corrigan and Fox were starting to notice the impact of theseinefficiencies on customer service, and it wasclear that this was the right time to implement a more disciplineddatabase system.FOXCORE HISTORYAs roommates, Corrigan and Fox had often talked about starting abusiness together. After graduatingfrom the University of Western Ontario, the two friends decided totest their entrepreneurial skills bystarting a small retail business selling inexpensive novelty itemsat music festivals and tradeshows.Inspired by his previous job in Western Canada selling children’stoys, Fox suggested they start by sellingBubble Guns, a hand-held electric toy that had been wildly popularat shows such as the CalgaryStampede (see Exhibit 1). While in Alberta, Fox had discoveredanother popular product, Arctic SkinCooling Towels, a product that once wetted stayed cool for severalhours (see Exhibit 2). Fox had alreadybuilt a relationship with the owner of the Arctic Skin CoolingTowels, and the two young entrepreneursdecided it would be a great product to bring to Ontario.Using a combination of their last names, Corrigan and Fox decidedto call their new venture Foxcore Retail(Foxcore). The two began searching for suppliers and negotiatingwith festival organizers to test the viabilityof their business idea. They began by setting up pop-up shops atsummer music festivals and weekend tradeshows across Ontario, demonstrating and selling their productsin-person (see Exhibit 3). Corrigan and Foxoften joked that they had become “carnies”—a slang term fortravelling carnival workers. Travelling todifferent cities across Ontario, attending events ranging fromlocal rib fests to fitness expos to large musicfestivals, the two partners quickly became familiar with the mostpopular event venues and their organizers.
As sales grew, their idea proved to be more successful thaneither of the two partners had imagined. Theywere often so swamped with setting up Bubble Guns for customersthat they were unable to demonstratethe Arctic Skin Cooling Towels. They hired several salespeople tohelp them handle the high demand,paying commissions to directly incentivize performance. They alsosensed a strong opportunity to addmore products to their lineup, tailored toward specific kinds ofshows. They began testing a variety ofnew products, such as emoji pillows, heating pads, andremote-controlled drones, and tried to figure outwhich products worked best at which shows.By its second year, Foxcore had expanded. Corrigan and Fox weremanaging up to three shows perweekend, with multiple booths at some venues. Things were goingvery well for the two entrepreneurs.However, their manual sales-tracking system was beginning to causeproblems. Sales were recorded onpaper, and then sales totals were transposed into Excelspreadsheets. Over time, more and more disparateworksheets were created that contained details such as eventinformation, sales, employee commissions,and product data. Keeping track of all the information was becominga major administrative hassle, anderrors were occurring. The partners realized that something had tobe done if they hoped to continueexpanding their business into year three. However, dealing with theproblem kept being delayed in favourof solving more immediate day-to-day business issues.THE PROBLEMIt was almost the beginning of the third summer for Foxcore. Thefirst two events of the season were onlya month away, and Corrigan and Fox were determined to bring someorder to their data collectionactivities. Information stored in different spreadsheets wasbecoming increasingly difficult to use forroutine business operations, and it was not providing strategicinsights. Despite having collected andstored essential data, the inability to access and analyze theinformation resulted in sub-optimal decisionmakingfor both short- and long-term planning.During the first two years of operations, each sales consultant hadkept a record of each sale on paper,which was then tallied each day to calculate commissions. In ahectic sales environment, however, not allsales were recorded by the consultants, which resulted in lostcommissions and unreliable inventoryestimates without a clear record of exactly what was sold. With 17full-time sales consultants hired for thethird season and over 28 products available for sale at variousshows across the province, Corrigan andFox needed to track exact details about which products were sold—bywhom, when, and where.Having completed a data management course at Ivey Business School,Corrigan knew that a customdatabase would provide a solution for tracking the different typesof business information the two partnerswere collecting. However, with less than 30 days remaining beforethe festival season began, Corriganwas running out of time. In the interest of saving time, heconsidered quickly building database tables onhis own and setting up relationships based on intuition (known as“hacking out” a database). However, heknew this could eventually lead to serious problems with efficiencyand data integrity. To do thisproperly, he needed to start with a solid conceptual datamodel.DESIGNING THE DATABASEThe first step was to understand the basic data requirements.Corrigan and Fox listed all key types ofinformation that could be captured by a database to represent theircompany’s operations. Their mainfocus was to design a system that could track events, salesconsultants, and individual sales of differentproducts. Through initial brainstorming, they identified six maincategories of essential information:
• Event: Several pieces of information were needed to be storedunder the category of events, such asthe event name, start and end dates, and a brief description. Ithad been helpful in the past to know theevent type, when determining which products to offer there. The twopartners had found that mostevents could be classified under one of seven categories: tradeshow, rib fest, waterfront festival,music festival, beer festival, sporting event, or streetfestival.• Venue: Each event had its own venue name and address, andsometimes a description with furtherinstructions such as where to park. It was important to store thisinformation separately, becausemultiple events could occur at the same venue during a givenseason.• Booth: There was always at least one booth set up at an event.Depending on the size of the show,however, additional booths could be set up to create more salesopportunities. Each booth was set upat an assigned location at the event (e.g., S11 referred to row S,section 11).• Product: Every type of product that was sold at an event had awholesale cost and a minimum sellingprice. Employees were encouraged to start with a higher price whenselling products and to “cutdeals” to help close sales.• Salesperson: Corrigan and Fox hired sales consultants to work inshifts at each booth. Each salesconsultant would spend the entire shift working at one booth.Employee information included firstname, last name, and usually the address and phone number of eachconsultant. Sales consultantscould work multiple shifts, but every shift would take place atonly one booth. Corrigan and Foxwanted to record in the database who was working at each boothduring the day, and at what time.• Sales: The goal of each employee was to generate sales throughouteach shift. Corrigan and Fox wantedto track the sales for each employee, for every shift worked. Itwas also important to record the productthat was sold, along with the quantity sold and the final sellingprice. Furthermore, they needed to beable to track sales by event (i.e., sales quantity of a particularproduct at a particular event).WHAT NEXT?Corrigan and Fox reviewed their list of requirements and felt thatit clearly represented what they neededto track. Corrigan knew that the next step would be challenging:break down the list into entities andattributes, and identify the relationships between them. This stepwas difficult because there was notnecessarily “one right answer”—different valid data models could bedesigned. That said, he also knewthat a properly designed data model would eliminate redundanciesand make the database implementationprocess much more efficient, and he wanted to take the time to doit right. Corrigan had done someresearch into common data modelling approaches and decided thatdesigning the model using Barker’snotation would be the best way to visualize the Foxcore database.1With all the tools in hand, Corrigan satdown and began sketching out a preliminary data model.
QUESTIONS: