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.