0

I have a simple ERD diagram as represented below for a supplier for inventory goods.

ERD for Goods Supplier

What would be the best way to turn this ERD into a Star Schema with one Fact Table and a few dimension tables?

To be specific, I'm having issues with the location table because both the customer and warehouse tables are sharing the same locationID.

Should I add locationID into the fact table and create a dimension table for location? But by doing so, wouldn't I need to add two types of locationIDs to the fact table, one for customer and one for warehouse since both might not necessarily have the same locationID.

Or should I add the attributes of the location table into the dimension tables of the customer and warehouse individually?

Improve this question
6
  • Why bother with star schema? What would you gain?
    – user212533
    Oct 12, 2021 at 18:36
  • Your Invoice entity doesn't make sense. According to this model, you can only invoice for a single item, and only for one unit of it (or is it for the entire inventory?) Also, your invoices are mutable: when the inventory changes, so do all past invoices. Your accountant won't be happy.
    – mustaccio
    Oct 12, 2021 at 19:19
  • @mustaccio Thank you for pointing out my mistake. I have updated the invoice table. I missed out an attribute for the invoice table, itemQty. The item qty was supposed to keep track of the amount of item sold in that invoice. You also did mention a good point if the inventory changes, do you have a suggestion?
    – Trelaquix
    Oct 12, 2021 at 20:08
  • @bbaird I'm trying to convert this ERD into a star schema because I want to denormalize the data to make it more scalable for a data warehouse. The database above is simplified to get a better understanding of how to convert an ERD to a star schema.
    – Trelaquix
    Oct 12, 2021 at 20:14
  • "a star schema because I want to denormalize the data" -- star schema does not imply denormalisation.
    – mustaccio
    Oct 12, 2021 at 20:26

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.