Model<\/span><\/h2>\n\n\n\nAt their core, relational models<\/span> are built for consistency and the purpose of eliminating redundancy in data<\/span> collection, that can lead to anomalies and inconsistencies. This is achieved by breaking data<\/span> into multiple tables<\/span> and relationships. Take for example a typical store<\/span> or shop, where a set of sales transactions may include customer<\/span> and product<\/span> information. In a relational schema<\/span>, typically the product<\/span> and customer<\/span> details would be separated into other tables<\/span> reducing repeating entries. Without doing this, we would have a lot of redundancy. Now consider products have categories, departments, models<\/span>. These data<\/span> would be further decomposed into other tables<\/span>. These sets of data<\/span> (in larger organizations) may be broken down further, for example different department locations, etc.<\/p>\n\n\n\nHere we have solved the problem of redundancy, but what if we want to retrieve core data<\/span> about a transaction and include information of all the above information. This would involve a number of joins across these tables<\/span>. Consider we\u2019re dealing with a large enterprise level relational database, this could potentially mean dealing with many tables<\/span>. Retrieving information may involve complex queries and expertise<\/span> of the entire model<\/span> to retrieve accurate results. In addition to this, performance<\/span> may be reduced by using multiple joins to retrieve data<\/span>.<\/p>\n\n\n\nStar Schema<\/span> Model<\/span><\/h2>\n\n\n\nIn contrast to the relational approach, a star schema<\/span> offers a flatter design. Rather than separating out data<\/span> to its most granular level into multiple relationships, a star model<\/span> is primarily organized around facts<\/span> and dimensions. Consider the transactions mentioned earlier as facts<\/span> and customers and products<\/span> as dimensions. A typical star model<\/span> will contain these two levels, which greatly simplify design. The downside is of course the redundancy issues we were trying to solve in the relational schema<\/span>. If the goal however is a top-down<\/span> approach focused around the efficiencies of reporting and simplification of use, then the star model<\/span> has the edge over a relational model<\/span>. The star model<\/span> is a flatter design than a relationship model<\/span>, therefore we reduce complexity and get to the data<\/span> we need in an easier fashion.<\/p>\n\n\n\nSometimes a star model<\/span> does require more granularity and more levels than the initial two, this type of configuration<\/span> is sometimes referred to as the snowflake schema<\/span>. To summarize, star schemas are flatter than their relational counterparts, their flatter design eliminates the need for entities<\/span> such as lookup<\/span> tables<\/span>, and they are easier to query. They do come at a cost of increased redundancy, however they should be considered a good option in terms of building sets<\/span> of data<\/span> for reporting, that otherwise may be complex to produce (or performance<\/span> hitting with multiple joins) in a relational schema<\/span>.<\/p>\n","protected":false},"excerpt":{"rendered":"There are two main philosophies that have become prominent over the past several decades with information management pioneered by Bill Inmon and Ralph Kimball. Nagesh & Cody (2005) provide an overview of these contrasting approaches, which are vastly different with their own merits and downsides. Inmon believes in building a large centralized enterprise-wide data warehouse […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7153],"tags":[],"post_series":[],"class_list":["post-9123","post","type-post","status-publish","format-standard","hentry","category-data-analytics"],"yoast_head":"\n
Relational vs Star Schema Model - Ian Carnaghan<\/title>\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n\t \n\t \n\t \n