CODEWITHSUNDEEP
ssmsdata-warehousestar-schema
ruskind
ruskind

Reputation: 227

Data warehousing more than one data type in a fact table

I'm working towards a Star Schema for a database which includes details about both results for individuals who have undergone training and assessment, and their feedback on the training. To maintain simplicity and performance it makes sense to limit the number of fact tables, so I was planning on using one fact table, and separate the feedback and score data using a dimension.

The issue is that some of the feedback data is in string format (written answers) as opposed to numerical format. How should I approach reconciling the two types of data into one fact table, or should I have two fact tables, one for string based feedback answers and one for the numerical scores. Or even would it make more sense in fact to have three fact tables, one for the scores, one for the feedback numerical data, and one for the feedback data in string format?

Upvotes: 0

Views: 46

Answers (1)

Marmite Bomber
Marmite Bomber

Reputation: 21095

The main driver of the modelling of the schema are the expected queries.

If you have a good imagination how the queries will be formulated, simple check all three options and compare the results.

If you can't anticipate the queries, probable the best way is to start with the scheme as 1:1 to your inputs and react if necessary.

Upvotes: 1

Related Questions