[UPHPU] Star and Snowflake Schemas

[Alvaro Carrasco]

Dave Smith wrote:
> Daniel C. wrote:
>> Just wondering if anyone here has experience implementing star or
>> snowflake schemas in an RDBMS, and (assuming someone has), whether
>> you'd be willing to tell me a bit about them.  I understand the
>> problems they're designed to solve, but I haven't been able to find a
>> good example of an implementation of either schema.
>
> I read this post and thought, "Wow. I should check out this new DB 
> principle that apparently is called 'star' and 'snowflake' schema." It 
> turns out that these are two common and simple RDBMS concepts if my 
> googling has proved accurate. I just had never named them this way 
> before. I'd imagine that 99% of all database schema are of the "star" 
> or "snowflake" variety. Here's an example of a star schema:
>
> Customer Table
> - ID
> - Last name
> - First name
>
> Address Table
> - Customer ID
> - Address Line 1
> - Address Line 2
>
> Purchase History Table
> - Customer ID
> - Item ID
> - Purchase Date
>
> Customer Credit Card Table
> - Customer ID
> - CCN
> - Billing Name
> - Billing Address
>
> In this example, the Customer Table would be at the center of the star 
> and the other 3 tables would be around the perimeter. May I inquire 
> what prompted this question?
>
> You could probably find implementations of these schema types in any 
> blogging software with a relational database backend. OSCommerce comes 
> to mind also, as does a myriad of other software.
>
> --Dave
>
I would have to say that that is NOT a star or snowflake schema. Star 
and snowflake schemas are not very common since they are only used for 
reporting, and in a lot of cases they're not worth the extra work of 
doing the Extract Transform and Load (ETL) process if you can query you 
transactional database (OLTP) for reporting.

This is a good article that describes creating a transactional schema 
(normalized), and then creating a reporting schema (star, snowflake) to 
aggregate the data:
http://www.ciobriefings.com/whitepapers/StarSchema.asp

Alvaro


Alvaro