0

I have a source data set with:

  1. customer
  2. customer_product_purchase
  3. customer_support_plan_purchase
  4. customer_support_request

All of them have a relationship such that a support request is raised against a plan and product purchase. And that a customer buys a support plan for a product (which the customer also buys).

In order to design a data warehouse schema for this, I was thinking of creating a single fact table, I thought of the following approaches:

  1. Having a consolidated fact table with customer_product_purchase, customer_support_plan_purchase and customer_support_request into one as they have a few common attributes (and few uncommon ones which can be kept blank for others). As I believe they are at the same granularity (purchase of product/support plan, raising a request against a support plan). This would mean losing some specific information as to make it generic, like product warranty and support plan validity under the same name validity
  2. Creating a fact table from customer_product_purchase and customer_support_plan_purchase which are inherently purchases, and can be kept together with some common and some uncommon attributes. The customer_support_request can be treated as separately.
  3. Creating a fact table around customer_support_request as it ties up to both the other tables, which can be the dimensions. However, this will mean that the dimensions will also grow at the same rate as the fact (which I have read, is an indicator of bad design).

So how can I deal with such a situation where the support plan, service request and product purchase can grow by themselves individually, is it best to just keep all of them separate? But because they (all or two of them) have similar granularity, shouldn't they be consolidated?

Improve this question
4
  • bit value, but general principles, design it in such a way that your queries end up simple.
    – danblack
    Jun 19, 2020 at 5:41
  • @danblack That makes sense, but I wanted to be sure that I'm not doing any clear blunders in design. Because to make querying simple, it can also make insertion/updates difficult. And I wanted to know how is such a case generally dealt with
    – Yankee
    Jun 19, 2020 at 6:42
  • 1
    I don't think support request has the same granularity as product and plan purchase. You could have multiple requests linked to the same plan through the life of a product, right? I believe support request should be in a separate fact table.
    – JeromeFr
    Jun 20, 2020 at 13:00
  • 1
    @JeromeFr Yes, you're right, I had a different understanding of what 'granularity' meant. Thanks
    – Yankee
    Jun 20, 2020 at 13:05

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.