sqldatabase-designdata-modelingdata-warehousestar-schema-datawarehouse

How to design star schema in a restaurant scenario where customer place food orders and each order contains multiple food items

Here I am having a FACT table, FACT_Orders and which has fields customer_id, order_id, total_cost, created_date, updated_date.

Also I have one more FACT table, FACT_Order_List to keep the items in the order which has fields order_list_id, order_id, item_id

Also I have the dimension tables DIM_Item and DIM_Customer to keep the arrtibutes of Item and Customer

From the FACT tables, I need to compute the most popular item and least popular item sold

Is this good design having two FACT tables and joining FACT tables for the computations?

Solution

  • Assuming that :

    An item has many orders and an order contains many items.

    you can have a fact record like below : CustomerId=5 ordered 2 items

    OrderId OrderListId ItemId CustomerId ItemPrice   TotalCost   CreatedDate
1       1           12     5          100         350         17-01-2021
1       1           15     5          250         350         17-01-2021

    You don't join fact tables. Facts are combined by summarizing each set of measures individually by common dimensions then joining the summarized sets on those dimensions.

    Your model can be like below:

    enter image description here

    Regading the updated_date in the fact :

    Fact tables should not be updated. There are several really good reasons why facts measure values should almost never be updated. ... Fact Table Processing: Preforming updates on tables can be a very costly operation on the SQL Server data warehouse database server! Fact tables can and should be very large.