Our client operates a small San Diego family business specializing in building and selling manufactured products for the personal, residential, commercial, and government sectors. 

In 2018, the CEO approached us with a unique business vision: to create an elegant e-commerce website where shoppers could build and order a customized product within a minute.

The company sells manufactured products with thousands of options, so this new e-commerce site would need to simplify the purchase process by allowing customers to choose their body size, material, color, and more. 

The CEO's vision was in line with the increasing trend of consumers building their own products online, similar to Tesla's virtual car customization tool. We faced several immediate challenges, including that our client's data consisted of over 5 million uncomputerized stock-keeping units (SKUs). 

The company did not have an extensive technical team or a clear roadmap. Despite these challenges, the CEO was determined to realize his vision.

 

Database Schema for E-Commerce Websites: Why We Chose This Project

Every project we accept represents a unique story about the customer, their background, and their aspirations as a company. Sometimes, we have to turn down projects because it’s simply not a good match. 

However, when we met the CEO of this small San Diego area family business, his passion and business vision were both palpable and compelling.

He had a single-minded focus: making his industry more customer-friendly, seamless, and efficient. His solution was offering shoppers a customized e-commerce portal for fully customizing their products.

This would not be an easy project; that much was immediately obvious! The best projects never are. But there were several reasons why we were determined to take it on:

The Challenge We Faced While Launching an SKU Dataset

The biggest and most immediate obstacle we faced was creating the SKU dataset and placing all the product attributes into an online format. The information available to us was a phonebook-sized product catalog dataset with over 5 million SKUs – not to mention the product information in the heads of the sales team. 

We had to be honest – this project posed some unique challenges. There were high-level business requirements, a large dataset, and many unknowns. But with our Agile experience, we believed we could pull it off.

The Vision Behind the Project

The CEO was the driving force behind our decision to work with this company. He was a consummate professional and visionary whose passion for serving his industry inspired us to want to step in and help him achieve this dream. 

A Rising Opportunity Towards Building an SKU Dataset

Our customer was extremely cautious—and with good reason. This was a reasonably large custom e-commerce website and a substantial investment for a small company. Their previous experience with a software vendor was less than ideal and delayed bringing the CEO’s dream to life. 

We knew we could nail this project but also needed to show we were different. We saw it as a unique opportunity to prove ourselves in a new industry where we had to solve unique business and technical challenges. 

We saw it as a chance to demonstrate our expertise in database schema design for e-commerce websites and our ability to build highly scalable product catalog datasets.

How We Achieved Building a Product Catalog Dataset for an E-Commerce Platform

The biggest challenge in building this customized e-commerce site was the SKU dataset. All the essential assets were in print form. The first task was building the online database containing all the various products and hundreds of attributes, such as body material, color options, engraving, and much more. 

This was the first time we had built this kind of SKU dataset. We often receive datasets for relatively uniform and tabular projects, with everything assigned to a particular set of attributes. 

Doing this manually was out of the question; that would require entering 5 million SKUs as line items in an Excel file – not a good use of time! Not to mention, in the long run, this was potentially not a sustainable model. We knew there had to be a better way, and we were up to the challenge of finding out how.

Our technical lead understood this project's importance to the client and fully invested himself and our team to get the job done right. He learned the business inside and out by studying their product catalog dataset and thoroughly interviewing each sales staff. He gathered a mountain of information to ensure he had all the pieces to orchestrate the project successfully.

Here are the steps we took and questions we asked:

Show me the data and how you differentiate between products? 

Our best resource was a printed catalog with 5 million product stock-keeping units (SKUs), which can also be identified as a product catalog dataset. The subject matter expertise of the sales team knew that catalog inside out. 

During his first visit to kick off the project, our technical project lead (TPL) spent time getting to know the business, building relationships with the sales team, and earning their trust. 

Organizing the product families

The next step was to identify the product families. This was accomplished by interviewing various sales team members, using a rigorous classification system, and some cleverness. 

Identifying the product attributes

Our technical lead dedicated most of his first three weeks to onsite interviews with the client sales team to understand and design the SKU dataset. His goal was to identify common attributes for the product families. 

One type of product family in the "Monkey Series," for example, has a common attribute of the material: steel. From that attribute, the remaining products on that document are all identified as permutations based on other factors such as size, type, color, etc.

Categorizing the product components

This step involved drilling down and understanding the vast number of product features and their differences. Besides the bulky catalog of 5 million SKUs, the best resource was the sales team. 

If the technical lead described the information he needed, a salesperson would go to the product catalog dataset with some attributes in mind and find a match. In other words, much of this exercise was understanding how the product components were modeled in the brains of the sales team.

Family of products that only differ in 3-4 attributes

As we went along in this project, certain patterns began to emerge. A particular series related to material—steel, for example—might have four products available, and one of those four has a specific feature on and another feature off, such as engraving. 

By the end of the three weeks, we knew a lot more about these products than we ever thought possible, and we were able to develop a highly scalable ecommerce product catalog database design to support the client’s business needs.

 

"This dataset allowed a consumer to select—and our customer to manufacture—a customized product that had never been bought or sold before!"

Three Weeks and Thirty-Three Documents Later

 

Three weeks of discussion with the sales team identifying product family attributes and categorizing them yielded a set of 33 SKU datasets, each with its unique set of characteristics:

 

1- One document had 2.5 million SKUs. If one attribute is changed, then the permutations of this product also change. In other words, if you had 10 attributes, each permutation of these 10 attributes would equal 2.5 million SKUs.;

2- The remaining 32 SKU datasets needed more common attributes to be placed into one document.

3- Overall, we identified 53 families of products. Each family had a specific set of attributes, and each attribute had a set of options to which this attribute could be set.

 

This level of complexity required a highly scalable and flexible database schema, which our team was able to achieve using Azure CosmosDB.

 

Once our SKU datasets were in place, we needed to find a way to computerize the data. Our customer technical lead still had to type the information into the database manually, but we were able to streamline the process by developing a custom data import tool.

 

After every modification, the data had to be validated to ensure accuracy, which was a time-consuming process, but it was essential to ensure the project's success.

 

Once we finally had our data schema in place and our data computerized, it was time to determine if it held up against the ultimate test: the sales team. They knew the products better than anyone else, so we asked them to try to break our data schema by coming up with the most obscure and unique products they could think of.

 

Our team relentlessly racked their brains—and the product catalog dataset—to find products that would stump us, but our data schema prevailed! After three weeks and countless iterations, we were happy to say we had out-customized the sales team.

The Product Catalog Dataset We Delivered

 

The biggest lift of this project was the SKU dataset. The rest of the project was straightforward once we condensed the 5 million SKUs into 33 documents. We built our UI using Angular 7 and then used .NET Core and CosmosDB on the backend. 

 

This effort resulted in an elegant, seamless e-commerce website, making ordering the client’s products more manageable. This SKU dataset allowed our consumer to select—and our customer to manufacture—a customized product that had never been bought or sold before!

The Benefits of Developing an Ecommerce Product Catalog Database Design

 

There were a number of obvious benefits that grew out of our engagement with our client. 

 

We accomplished the formidable task of distilling 5 million SKUs into 33 highly customized SKU datasets. This stretched us as a team but ultimately made us stronger.

We created a robust but flexible SKU dataset that can easily be adapted to meet the changing needs of our client's business. Attributes can easily be turned off and on, and entering additional products into the dataset is easy.

 

The intense customization of this SKU dataset and ecommerce product catalog database design helped our client's sales team discover and sell products they didn't even know they had in their own catalogs.

 

This was indeed a one-team approach. Despite the project's complexity, we managed to stay within the client's budget and meet our deadlines.  

The Takeaways  

 

What seemed like an impossible task became one of our most rewarding projects ever. We were asked to design a seamless e-commerce website to enable shoppers to build a customized product within a minute. 

 

How could we step in and add value? The client was highly cautious—and with good reason. It was a sizeable investment, and their previous experience with a software vendor was less than ideal. Not to mention, all we had to work with was a telephone-book-sized product catalog dataset containing 5 million SKU datasets. 

 

In the end, we made the right choice to work with this client. Despite the challenges, it was entirely worthwhile to see their dream come to life before their eyes. We could not have asked for a more committed, passionate team to work with. We were so inspired to witness their dedication and commitment to this project. 

 

This is what we are all about! Making our clients happy by building elegant, seamless web and mobile experiences to strengthen their businesses gets us out of bed every morning. Knowing that together, we’re helping to make the world a better place drives our team to relentlessly pursue excellence every day. Contact us and get a free consultation.