How combine user device data and fact table design?

Question

It is needed to store following information:

1. User device data like device id, device brand, screan size, type (pc, tablet, mobile).
2. Actual facts, like odrder id, total cost, goods count, goods ids etc.

How where to put user device data - fact or dimension table?

If I put it to fact table, I have a lot of duplicates, e.g. each row have to cotained detailed information about device, with is mostly the same for one user.

If I put it to dimesion table, I have to update dimension table - add new device or update older.

Important note here is analytical system designing not only analyze user behaviour, but to see where data is not gather. So there can be a situation when different application versions provide different "fullness" of information - 1st version gather 50% of user device, 2nd - 75%, 3rd - 99%. So I have to update (not only insert) data in devices table.

Answer 1

You need at least two dimensions i.e. User and Device with Snowflake Schema. The user dimension should reference the Device table with Device ID.

User will just keep a list of users. It will use foreign key to reference the Device table(on say Device ID). You might go for any SCD type to handle data changes. But it will definitely better in comparison to if all the device fields were on a single table.

Device table will hold the different types of Devices and their attributes.

The fact would then just need to reference (UserID, DeviceID) combination from User table.