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? Commented 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. Commented 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? Commented 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. Commented Oct 12, 2021 at 20:14
  • "a star schema because I want to denormalize the data" -- star schema does not imply denormalisation. Commented Oct 12, 2021 at 20:26

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.