-1

I'm new to database design. I'm trying to design a data model for car sharing company. They have more than one business model like car sharing, scooter sharing, delivery.

Here is my dilemma, which choice should I choose:

  1. (database warehouse bus architecture) Should I create more than one fact table for different businesses? different fact table represents one business model and share some conformed dimensions?

  2. (one star schema) Or should I store the different business names(car sharing, scooter, delivery) in one dimension table call it product?

Thank you!!!!

emfffff
  • 1
  • 1
  • it depends on many things, and there is not 1 answer. what you asking is related to normalize. There are three main reasons to normalize a database. The first is to minimize duplicate data, the second is to minimize or avoid data modification issues, and the third is to simplify queries. – Francois Taljaard Oct 26 '20 at 15:47
  • Thanks for your response, that was not what I asked. I just edit my question to make it more clear. – emfffff Oct 26 '20 at 21:13

1 Answers1

1

the way I would approach this is as follows:

  1. List all your facts and their associated dimensions
  2. Determine the level of commonality of dimensions across all facts

If your facts all have roughly the same dimensions then you could put them all in one fact table. There is no hard and fast rule for for this but I would say that if a fact has more than about 2 dimensions that are not used by other facts (or are used by other facts but not by this fact) then it needs to be in its own fact table.

If you decide that you could put more than one fact in a single fact table then it becomes a judgement call. Things to consider when making the decision include (obviously not a definitive list):

  • Would you ever want to query different facts at the same time, in the same query? If so, then having them in 1 fact table makes this easier e.g. count by fact type by date
  • Are your data volumes so large (or likely to be in the future) that having a single fact table is going to cause issues? If so, then having separate fact tables at the start, rather than having to split them in the future, makes sense
  • Security: do you need to restrict access to different facts to different groups of people. If so then having separate fact tables would probably makes this easier

My personal preference, assuming querying different facts at the same time wasn't a major requirement (first bullet point above), would be to use separate fact tables. While there is more ELT involved it shouldn't be significant - once you've built the logic for the 1st fact table then all the others should be "copy-and-paste" plus a small amount of editing. Having different fact tables is more flexible and is probably going to cause you fewer issues in the future

NickW
  • 8,430
  • 2
  • 6
  • 19