Understand the Store Data Model

Dorothea starts her data model journey by setting up the objects that Alpine Group can use to capture data about its retail stores. For this store-level data, she uses a combination of standard and consumer goods objects.

Standard objects:

• Account
• Location

Consumer Goods objects:

• Retail Store
• Retail Store Group
• Visit

Her store data model looks like this.

The store data model starts with the Account object. The Account object stores information about Alpine Group’s retail business accounts including billing and shipping payment information and store hierarchies.

Using the Account object, Dorothea creates one record for Northern Trail Outfitters. That’s because Alpine Group doesn’t have financial transactions with NTO’s individual retail stores. Instead, Alpine Group bills the NTO account for all of its products NTO sells at its stores or on its web portal. Dorothea also captures the entire account hierarchy for NTO stores in the Account object.

Dorothea uses the Retail Store object to capture the information about each physical NTO store that Gustavo, sales manager for the NTO account, needs in order to plan visits and sell products.

The sales managers at Alpine Group base their route planning and visit assignments on the location of retail stores, among other factors. The store’s location, driving directions, and other details are stored in the Location object. The Location object is a foreign key referenced in the Retail Store object.

A primary key uniquely identifies a record in the relational database table, and a foreign key refers to the primary key of another table. So while Account is a primary key in its database table, it becomes a foreign key field in the Retail Store database table.

There are many other granular details that Alpine Group must capture for its retail stores in the Retail Store object. Those store-level details include:

• Operating hours and preferred visit hours for each day of the week. During the preferred time, the store manager is available to meet with the delivery person or the field rep who comes to promote Alpine Group’s products.
• Store priority based on how critical it is to maintain Alpine Group’s brand presence in the store. Alpine Group can focus more on the high-priority stores by arranging weekly or daily visits so that inventory and placement of products are always optimal.
• Preferred delivery method, such as delivery by truck or van.

Each record in the Retail Store object contains a reference to the account, the location, and other details.

You might remember that Alpine Group has an army of dedicated field reps who carry out various tasks when they visit a retail store.

Their activities depend on two critical things: the store’s business hours and the availability of the store managers to meet with the field reps.

Dorothea can store that information in two objects.

• Operating Hours: Dorothea can use the Operating Hours object to define two separate records for business hours and preferred visit hours for each retail store. This object stores information related to timezone and time slots. The time zone information is critical because Alpine Group sells to stores across the globe.
• Time Slots: This object stores information for each day of the week. You can define the business start time and end time for a day, and mark a day as a non-working day.

The Operating Hours object and the Time Slots object have a one-to-many relationship.

As you can see in the table in this unit, a retail store record holds a foreign key reference to Preferred Visit Hours and Operating Hours, for easy access. Neat, right?

Most NTO stores in a store group look the same, especially when it comes to the specialty stores. Store layout is critical to Alpine Group’s planning. Placement of products plays a major role in attracting customers and promoting brand awareness.

All NTO specialty stores have a primary display for Alpine Group products, usually the first two aisles in a store. There are also secondary locations such as endcaps and checkout counters for displaying specific products. Each in-store location is stored in the In-Store Location object. The In-Store Location object and the Retail Store object share a primary-detail relationship.

The consumer goods data model enables Alpine Group to arrange data into manageable clusters. There are multiple points of commonality between retail stores that Alpine Group can use.

Dorothea considers a lot of factors to see what its retail stores have in common. Take all the Northern Trail Outfitters stores for instance. Here are just a few significant aspects about the NTO stores that she analyzes.

• Size: From the giant supermarkets to the independent chain of specialty stores, NTO stores vary in size.
• Store type and product range: NTO targets different customers through different stores. Alpine Group sells a select catalog of products to the NTO specialty stores. In fact, Alpine Group has consciously created a limited range of products for the NTO specialty stores. These products cater to the health-conscious customers who want nothing but the best. Alpine also has a line of activewear, superfoods, and energy drink products that are not so niche and sold in bulk at most NTO stores.

After analyzing the gamut of grouping options, Dorothea determines the most critical factors driving Alpine Group’s business decisions.

• Type and range of products sold at stores
• Type of promotions targeted for specific stores
• Stores with similar layouts
• Stores with similar business agreements

Dorothea decides to create two primary store groups for NTO.

• NTO Specialty Stores
• NTO Miscellaneous Stores

She puts all information related to each store group in the Retail Location Group object. Each retail store that falls in the group contains a foreign key reference to the Retail Location Group.

She can always create additional store groups when needed. The stores in the miscellaneous group have a lot of differences, and that may lead her to create multiple store groups later on. You can create as many store groups as you want, but you can’t have a hierarchy in the Retail Store Group object.

Important

The object is called Retail Location Group in the data model, but the object is named Retail Store Group in the Retail Execution app. Going forward in this module, we refer to this object as Retail Store Group.

The data model gives Alpine Group the flexibility to define and track performance metrics at the store group level. This increases efficiency in managing data.

A retail store can be a part of one or more retail store groups.

Now that she’s put the store data model in place, Dorothea can move onto the next big piece of the puzzle: products. She needs to consider how to manage the big basket of products that Alpine Group sells at various retail outlets. Product classification is key to operational efficiency. We look at the product data model in the next unit.