title: Table Design for Data with multiple Granularity
body: <p>I have been trying to understand data modelling and warehousing, Given the best practices of having only one type of granularity in the table can I have one table store both the low granular data with the aggregate data.</p>
  <p>2 Table Structure</p>
  <p>Table 1</p>
  <pre><code>TransactionID   Transaction_Dt  ProductID   Items   Cost_Total
  11111           1/1/2020        1           10      100
  11111           1/1/2020        2           5       200
  11111           1/1/2020        3           4       400
  11111           1/1/2020        4           5       100
  11111           1/1/2020        5           6       600
  11111           1/1/2020        6           10      100
  </code></pre>
  <p>Table 2 (Aggregated)</p>
  <pre><code>TransactionID   Transaction_Dt  Total_Items Cost_Total
  11111           1/1/2020        40          1500
  </code></pre>
  <p>One table Structure</p>
  <p>Aggregate Data in the table</p>
  <pre><code>TransactionID   Transaction_Dt  ProductID   Items   Cost_Total  Type
  11111           1/1/2020        1           10      100         ind_Item
  11111           1/1/2020        2           5       200         ind_Item
  11111           1/1/2020        3           4       400         ind_Item
  11111           1/1/2020        4           5       100         ind_Item
  11111           1/1/2020        5           6       600         ind_Item
  11111           1/1/2020        6           10      100         ind_Item
  **11111         1/1/2020        ALL         40      1500        all_Item**
  </code></pre>
  <p>Here we have one record for the entire transaction with the sum of all items and sum of all cost.</p>
  <p>Can anyone help me on the Cons of the 2nd approach where we have aggregated data in the same table</p>