Reuse or replicate dimensions?

Hello,

I have several fields on my fact table that make reference to the completeness of different data (i.e. personal data, test data ...) and they can take three values (No data, complete, incomplete). What would be a better practice, to reuse the same dimension table for all this fields, or to have separate dimensions tables?

On one hand, separate dimensions allow you to modify the values for one field if it was necessary, but on the other hand, if this values are not modified (what is the most probable), you avoid to have identical tables.

I don't know what the way to go is, to reuse as many table as possible or to have one tabld for each dimension (field) (except for thr time dimension).

Thank you.

Why not add this attribute to the dimension that is having its completeness measured?

I think I don't understand your answer.

The fact table have have a lot of fields with foreign keys that make reference to the dimension tables. I.E. personal data (-1,0,1) and in the dimension table (-1 -> No data, 0 -> Incomplete, 1 -> Complete).

If you are suggesting degenerate dimensions, this is only the easiest example I have, so it's not a real option.

Nick is suggesting that completeness is an characteristic of the dimension. In other words, you store the completeness flag on each dimension table.

Storing as a degenerate on the fact doesn't appear to make sense. Could you elaborate why you think it belongs on the fact table?

Ok. There's no a dimension for these data. The only thing we know it's if something is complete, but we don't have such data in the database. It's just for statatistics, so we don't have the real data in this model.

In that case, a degenerate value on the fact should be fine.