I’ve designed a star schema for Facebook Marketplace with the following tables:
dim_Users – Users on facebook(buyers)
dim_Listings – Marketplace listing details
dim_seller – Users(sellers) who created listing
dim_Date – the mandatory date dimension
fact_Transaction – Table for tack transaction between buyer and seller(This table will only have entry if we conclude that product was sold to some buyer).
Attributes:
is_business_acount( This field is to track whether seller is running small business from home, this can be calculated by total active listings a seller has. If total listings are greater than certain threshold, then we can assume it’s a small business. I’ve added this field to ignore outliers while calculating certain metrics)
I have below concerns-
Can I use user_id as PK for both Users & Seller table? (Seller has to be facebook user to create listing)
Number of records in my fact table will be <= number of records in listing dimension table. Is that ok?
Please let me know if this design make sense. Suggest any optimizations or improvements?