Is a snowflake better than indexing?

Question

Here is the problem: I have a sales information table that contains sales information that has columns such as (Primary Key ID, Product Name, Product ID, Store Name, Store ID, Sale Date). I want to do an analysis, for example, drill and drill down by date of storage / product / sales.

There are two design options that I think of

• Create an individual index in columns such as product name, product identifier, store name, store identifier, sale date;
• Using the data warehouse snowflake model, processing the current sales information table as a fact table, and creating a product, storage, and sales size table.

To have better analysis performance, I heard that the snowflake model is better. But why is it better than an index on related columns in terms of database design?

thanks in advance Lin

Answer 1

Knowing your application usage patterns and what you want to optimize is important. Here are a few reasons (among many) to choose one over the other.

Normalized Snowflake PROs Objects:

Faster queries and lower disk and memory requirements . Due to the fact that each normalized row has only short keys and not longer text fields, your main fact table becomes much smaller. Even when an index is used (if the query cannot be directly answered directly by the index itself), a partial scan of the table is often required, and less data means less read on disk and faster access.

Simpler changes and improved data integrity . Say the repository changes its name. In a snowflake, you change one row, while in a large denormalized table you have to change it every time it appears, and you will often have spelling errors and several variations of the same name.

Denormalized Wide Table PROs:

Fast loading of one record. . When you most often load only one record or a small number of records, all of your data on one line will contain only the missed cache or disk that was missed, while in a snowflake, the database may have to be read from several tables on different disk platforms. This is more like the way NoSQL databases store their "objects" associated with a key.