Re: Data Warehouse Design Help
Posted by: Rick James
Date: November 07, 2013 10:20PM

http://mysql.rjweb.org/doc.php/ricksrots#data_warehouse
and, especially, the link to
http://mysql.rjweb.org/doc.php/datawarehouse

> has a date fk

Do not normalize dates.

First envision the fact table(s). Then "normalize" bulky, repetitious fields.

> Should I take the subscription_payment_number and put it in the fact table as well?

Never (in general) update a Fact table.

> transaction dimension and add the transaction_type

How many transaction types are there? Probably only a few. Use an ENUM, not a dimension table.

> wanted to give as much detail as possible

Well, one details is missing: SHOW CREATE TABLE.

> User - All user information including user_level which is defined by the last transaction. 3=active, 4=declined, -1=did not complete signup.

You are implying that there is a "transaction" table, which would be a "Fact" table. This "User" table seems to have the information about, and status of, each user. Transaction table contains history; User table contains 'current' status.

> Having a dimension table seems like I'm normalizing??

I think of those as being synonymous.

Foreign keys -- Don't use them. They are a performance burden on the system. Instead, debug your code.

Normalization serves two important purposes in DW:
* Reducing bulk, hence improving performance
* Centralizing and dedupping information (eg, user address shows up only once) to make it easier to update ancillary info.

Options: ReplyQuote


Subject
Written By
Posted
November 06, 2013 02:45PM
Re: Data Warehouse Design Help
November 07, 2013 10:20PM
November 13, 2013 03:57PM
November 14, 2013 05:53PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.