INTERNET APPLICATION DEVELOPMENT
MID MARKET ERP DEVELOPMENT
by Sheila Zhang
Databases designed for businesses typically focus on data manipulation. Normalized relational databases tend to increase the amount of tables, to reduce the space required for data storage, while being updated to reduce redundancy. For example: a customer table holds the customer ID and address information for each customer, and the sales order table only keeps the customer ID, not the address information. Therefore, when a customer’s address changes, only the customer table is updated. This increases performing costs of an additional query due to the need of joining multiple tables.
For a business with more than one database, developers commonly create a data warehouse to analyze the data more efficiently. To solve this problem, programmers commonly use the Star schema and Snowflake schema in the design of a data warehouse.
Microsoft's description of a Star schema is best described by this graphic:
The fact table is in the middle of the schema. The dimension tables surround the fact table. This design approach effectively handles simple queries.
If the dimension tables are normalized for look-up tables, such as the Product Subcategory and Geography tables in this image, the star resembles a snowflake.
As you see, within one data warehouse you may have multiple Star and Snowflake schemas. Or, when both are present, you have a Dimensional Model.
Here is a few comparisons of the Star and Snowflake schema:
I hope you find this information helpful as you work to identify your business needs. Please let me know if you have any questions on database design.